Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 7th, 2011, 01:40 PM
Registered User
 
Join Date: Apr 2011
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default VBA Connection String to Excel Error

I have used the code below to create a connection string an excel workbook so that I can import data into access. This worked great last year but this year after I had made changes to the excel workbook and some minor changes to my form I am getting the error message: 'Error No: -2147467259; Description; Unspecified error'. This was intermittent at first but seems to be constant now. The vba I use to check the workbook to make sure there is data is below. Once it is verfied i do several imports usine the same code for the different recordsets.


Private Function bolAppIDEntered(strFile As String) As Boolean
Dim cnMainDownloadInsert As ADODB.Connection
Dim rsMainDownloadInsert As ADODB.Recordset
Dim strMainDownloadSQL As String
Dim strInsertSQL As String
Dim intCount As Integer
Dim strAppID As String

bolAppIDEntered = True ' AppID has been entered on the checklist page of application
' Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties="Excel 12.0;HDR=YES";
Set cnMainDownloadInsert = New ADODB.Connection
With cnMainDownloadInsert
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strFile & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With

strMainDownloadSQL = "SELECT * FROM [App-Download$A2:BT3]"
Set rsMainDownloadInsert = New ADODB.Recordset
rsMainDownloadInsert.Open strMainDownloadSQL, cnMainDownloadInsert
intCount = rsMainDownloadInsert.RecordCount

strAppID = Nz(rsMainDownloadInsert.Fields.Item("AppID"), "")
If IsNull(strAppID) Or strAppID = "" Then
bolAppIDEntered = False
End If

rsMainDownloadInsert.Close
cnMainDownloadInsert.Close

Any ideas on what maybe be causing this?
Reply With Quote
  #2 (permalink)  
Old April 8th, 2011, 04:59 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

Please check the following link on different options for connecting to Excel:
http://www.connectionstrings.com/excel
__________________
Om Prakash Pant
Click the "Thanks" button if this post helped you.
Reply With Quote
The Following User Says Thank You to om_prakash For This Useful Post:
gemost (April 11th, 2011)
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connection String with Excel file mateenmohd Classic ASP Databases 8 January 25th, 2013 02:49 AM
#NAME? error running Excel 2003 VBA in Excel 2007 steveburn Excel VBA 0 October 24th, 2009 08:47 AM
Connection String error in ASP picky Classic ASP Databases 1 October 19th, 2007 07:37 AM
connection string in excel sharcfinz SQL Language 2 May 7th, 2007 01:13 AM
The BeerHouse 'Connection String Error' nesrine C# 1 March 21st, 2007 01:28 AM



All times are GMT -4. The time now is 10:54 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.