Table format - connection string problem
 

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

Table format - connection string problem
 

You're not connecting to a Workbook. You're connecting to an Access Database
therefore no need for ";Extended Properties=Excel 8.0;" When using ADO or
connecting to any database I recommend using Connection.udl to create the
connection string. Open Notepad, File, SaveAs, "Connection.udl".

use Connection.udl to build your connection string, Then right click
connection.udl,
open with notepad and copy your connection string.

"IgorM" wrote:

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