I
IgorM
Hi
When I run the code below I get "External table is not in the expected
format" error at line rsData.Open sSQL, sConnect, adOpenForwardOnly,
adLockReadOnly, adCmdText
Option Explicit
Public Sub SelectFromAccess()
Dim rsData As ADODB.Recordset
Dim sPath As String
Dim sConnect As String
Dim sSQL As String
'Clear the destination worksheet
Sheets(1).UsedRange.Clear
'Get the database path (same as this workbook)
sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
'Create the connection string
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath &
"Gemini.mdb;Extended Properties=Excel 8.0;"
'Build the SQL query
sSQL = "SELECT DISTINCT TurnoverPrecedingYears.[Nosnik kosztow - numer] FROM
TurnoverPrecedingYears;"
'Retrieve the data using ADO
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not rsData.EOF Then
Sheets(1).Range("a1").CopyFromRecordset rsData
Else
MsgBox "No data located.", vbCritical, "Error!"
End If
rsData.Close
Set rsData = Nothing
End Sub
But if I delete Extended Properties=Excel 8.0 all is ok. Why is that? I run
the code above in excel 2007 but I'll also need to use it in excel 2003.
Kind regards
IgorM
When I run the code below I get "External table is not in the expected
format" error at line rsData.Open sSQL, sConnect, adOpenForwardOnly,
adLockReadOnly, adCmdText
Option Explicit
Public Sub SelectFromAccess()
Dim rsData As ADODB.Recordset
Dim sPath As String
Dim sConnect As String
Dim sSQL As String
'Clear the destination worksheet
Sheets(1).UsedRange.Clear
'Get the database path (same as this workbook)
sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
'Create the connection string
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath &
"Gemini.mdb;Extended Properties=Excel 8.0;"
'Build the SQL query
sSQL = "SELECT DISTINCT TurnoverPrecedingYears.[Nosnik kosztow - numer] FROM
TurnoverPrecedingYears;"
'Retrieve the data using ADO
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not rsData.EOF Then
Sheets(1).Range("a1").CopyFromRecordset rsData
Else
MsgBox "No data located.", vbCritical, "Error!"
End If
rsData.Close
Set rsData = Nothing
End Sub
But if I delete Extended Properties=Excel 8.0 all is ok. Why is that? I run
the code above in excel 2007 but I'll also need to use it in excel 2003.
Kind regards
IgorM