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?
|
