Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Connection String - How to hide it? | Excel Discussion (Misc queries) | |||
Pivot Table connection String | Excel Programming | |||
Chartspace Data Source and Connection String Problem | Excel Programming | |||
oledb connection string problem in vb.net | Excel Programming | |||
What is the connection string ? | Excel Programming |