Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADOImportFromAccessTable
Hi I am trying to use ADO to import data from Access to Excel (Procedure
below) when I run it the Macro Box is activated but is empty i.e. does not contain the procedureI managed to do it the other way round using ADO to export data from Excel to Access, that worked fine. Do I have to use a different Reference ie ActiveX Data Objects x.x Object Library for the Access to Excel , or do I put the code in the Access Module. Thanks for any help.Sub ADOImportFromAccessTable(DBFullName As String, _ TableName As String, TargetRange As Range)' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _ "TableName", Range("C1")Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= C:\Documents and Settings\Me\Desktop\db4.mdb" & ";" Set rs = New ADODB.Recordset With rs ' open the recordset .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all records .Open "SELECT * FROM " & TableName & _ " WHERE [FieldName1] = 'Mailing List ID'", cn, , , adCmdText ' filter records ' RS2WS rs, TargetRange ' write data from the recordset to the worksheet ' optional approach for Excel 2000 or later (RS2WS is not necessary) For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data End With rs.Close Set rs = Nothing cn.Close Set cn = NothingEnd Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADOImportFromAccessTable
The general approach looks OK to me (with a few details in question - see
below). You don't need any different reference: the ADO library is independent of the calling application. And you should not need to put the code in Access, it makes more sense to keep it in the spreadsheet. So the questions: - Why are you calling rs.Open twice? Normally ADO won't allow this unless you first close the already open recordset, so I must be missing something. - You must have left out some code, since it goes directly from your main procedure to defining the RS2WS procedure, and that part of the code seems to be pretty critical to understanding you you get your data from the recordset into your RS2WS routine. - Are you sure you have your connection string set properly? Have you tested to see if you are getting the connection? Also, try breaking the code after the recordset is returned and use the VBA debugger to take a look at what you got - is it returning the records you expect? "Subs" wrote: Hi I am trying to use ADO to import data from Access to Excel (Procedure below) when I run it the Macro Box is activated but is empty i.e. does not contain the procedureI managed to do it the other way round using ADO to export data from Excel to Access, that worked fine. Do I have to use a different Reference ie ActiveX Data Objects x.x Object Library for the Access to Excel , or do I put the code in the Access Module. Thanks for any help.Sub ADOImportFromAccessTable(DBFullName As String, _ TableName As String, TargetRange As Range)' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _ "TableName", Range("C1")Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= C:\Documents and Settings\Me\Desktop\db4.mdb" & ";" Set rs = New ADODB.Recordset With rs ' open the recordset .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all records .Open "SELECT * FROM " & TableName & _ " WHERE [FieldName1] = 'Mailing List ID'", cn, , , adCmdText ' filter records ' RS2WS rs, TargetRange ' write data from the recordset to the worksheet ' optional approach for Excel 2000 or later (RS2WS is not necessary) For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data End With rs.Close Set rs = Nothing cn.Close Set cn = NothingEnd Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADOImportFromAccessTable
Subs wrote: (Procedure below) when I run it the Macro Box is activated but is empty i.e. does not contain the procedure Sub ADOImportFromAccessTable(DBFullName As String, _ TableName As String, TargetRange As Range) Your Sub does not appear in the Macro dialog because it has arguments. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|