Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - Access to Excel
Trying to use ADO to move some data to Excel
Excel shows an error when: Dim Connection As ADODB.Connection is executed. This is the program: Sub AccessToExcel() Dim DBFullName As String Dim Cnct As String, Src As String Dim Connection As ADODB.Connection Dim Recordset As ADODB.Recordset Dim Col As Integer Cells.Clear ' Database information DBFullName = "E:\db.accdb" ' Open the connection Set Connection = New ADODB.Connection Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;" Cnct = Cnct & "Data Source=" & DBFullName & ";" Connection.Open ConnectionString:=Cnct 'Create RecordSet Set Recordset = New ADODB.Recordset With Recordset Filter Src = "SELECT * FROM DB1 WHERE DB2 = 'Starting'" Src = Src & "and Year = '2001'" ..Open Source:=Src, ActiveConnection:=Connection ' Write the field names For Col = 0 To Recordset.Fields.Count - 1 Range(A1).Offset(0, Col).Value = _ Recordset.Fields(Col).Name Next 'Write the recordset Range(A1).Offset(1, 0).CopyFromRecordset Recordset End With Set Recordset = Nothing Connection.Close Set Connection = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - Access to Excel
hi
connection may be a reserved word like string. set string as string???? try this Dim Con as ADODB Connection 'no dot also dim rs as ADODB Recordset 'again no dot also Dim col as long edit the rest of your code to reflect above. everything else "looked" ok BUT i didn't not test. Regards FSt1 regards FSt1 "Philosophaie" wrote: Trying to use ADO to move some data to Excel Excel shows an error when: Dim Connection As ADODB.Connection is executed. This is the program: Sub AccessToExcel() Dim DBFullName As String Dim Cnct As String, Src As String Dim Connection As ADODB.Connection Dim Recordset As ADODB.Recordset Dim Col As Integer Cells.Clear ' Database information DBFullName = "E:\db.accdb" ' Open the connection Set Connection = New ADODB.Connection Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;" Cnct = Cnct & "Data Source=" & DBFullName & ";" Connection.Open ConnectionString:=Cnct 'Create RecordSet Set Recordset = New ADODB.Recordset With Recordset Filter Src = "SELECT * FROM DB1 WHERE DB2 = 'Starting'" Src = Src & "and Year = '2001'" .Open Source:=Src, ActiveConnection:=Connection ' Write the field names For Col = 0 To Recordset.Fields.Count - 1 Range(A1).Offset(0, Col).Value = _ Recordset.Fields(Col).Name Next 'Write the recordset Range(A1).Offset(1, 0).CopyFromRecordset Recordset End With Set Recordset = Nothing Connection.Close Set Connection = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - Access to Excel
Dim Con as ADODB Connection
with a space or without a space gives me an error and: Dim Con as ADODB.Connection is not working also. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - Access to Excel
Have you set a reference to the Microsoft ActiveX Data Objects Library?
-- HTH Bob "Philosophaie" wrote in message ... Trying to use ADO to move some data to Excel Excel shows an error when: Dim Connection As ADODB.Connection is executed. This is the program: Sub AccessToExcel() Dim DBFullName As String Dim Cnct As String, Src As String Dim Connection As ADODB.Connection Dim Recordset As ADODB.Recordset Dim Col As Integer Cells.Clear ' Database information DBFullName = "E:\db.accdb" ' Open the connection Set Connection = New ADODB.Connection Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;" Cnct = Cnct & "Data Source=" & DBFullName & ";" Connection.Open ConnectionString:=Cnct 'Create RecordSet Set Recordset = New ADODB.Recordset With Recordset Filter Src = "SELECT * FROM DB1 WHERE DB2 = 'Starting'" Src = Src & "and Year = '2001'" .Open Source:=Src, ActiveConnection:=Connection ' Write the field names For Col = 0 To Recordset.Fields.Count - 1 Range("A1").Offset(0, Col).Value = _ Recordset.Fields(Col).Name Next 'Write the recordset Range("A1").Offset(1, 0).CopyFromRecordset Recordset End With Set Recordset = Nothing Connection.Close Set Connection = Nothing End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - Access to Excel
I put in ToolsReferences
Added: Microsoft Access 12.0 Object Library Microsoft ActiveX Data Objects(Multi-dimensional) 6.0 Library there are others but they conflict with each other. With these additions it still does not work. "Bob Phillips" wrote: Have you set a reference to the Microsoft ActiveX Data Objects Library? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO - Access to Excel
Not the Multi-dimensional library, that is for OLAP cubes. You want
Microsoft ActiveX Data Objects 2.n Library as I stated. -- HTH Bob "Philosophaie" wrote in message ... I put in ToolsReferences Added: Microsoft Access 12.0 Object Library Microsoft ActiveX Data Objects(Multi-dimensional) 6.0 Library there are others but they conflict with each other. With these additions it still does not work. "Bob Phillips" wrote: Have you set a reference to the Microsoft ActiveX Data Objects Library? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access to Excel: Identify attachment field in access database | Excel Programming | |||
Trust Access to Visual Basic Project - Access to Excel and back | Excel Programming | |||
How do I access the access data via Excel 2002 with auto update ? | Excel Programming | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) |