Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
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?
 
Old April 8th, 2011, 04:59 AM
Friend of Wrox
 
Join Date: May 2004
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.
The Following User Says Thank You to om_prakash For This Useful Post:
gemost (April 11th, 2011)





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.