VBA and ADO

[sheena]

I have created some tables using Access DB, I need help in presenting table data in Excel using VBA and ADO. I have some functions but are only in SQL. I need to display my tables in Excel.

[lifeson]

This may help
[VBA]Sub GetTable(ws As Worksheet, filter As String)
Dim mdb As String
Dim Cnct As String
Dim Connection As ADODB.Connection
Dim recordSet As ADODB.recordSet
Dim col As Integer
Application.Cursor = xlWait
Application.ScreenUpdating = False
mdb = 'name of your database file

'Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & mdb & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set recordSet = New ADODB.recordSet

With recordSet
' filter is passed from called function
.Open Source:=filter, ActiveConnection:=Connection
' Write the field names
ws.Cells.Clear

For col = 0 To recordSet.Fields.Count - 1
ws.Range("A1").Offset(0, col).Value = recordSet.Fields(col).Name
Next
' Write the recordset
ws.Range("A1").Offset(1, 0).CopyFromRecordset recordSet
Set recordSet = Nothing
End With

Connection.Close
Set Connection = Nothing
Application.Cursor = xlDefault

End Sub
[/VBA]

[sheena]

Hi Lifeson,

Ive tried this VBA and not working, may be what am doing is wrong, my DB file is called Customers and one of my table is called Year 2008. Try fixing them and I see how to go about it. I need to display all data in this db file and all tables in it and insert data in some tables.

[lifeson]

Can you post an example of your code?

[sheena]

Hi Lifeson,

Here attached is my Access file, I need to display the data in Excel and select some columns to create graphs, look at it and see if that helps.

[sheena]

Hi Lifeson,

Here attached is my Access file, I need to display the data in Excel and select some columns to create graphs, look at it and see if that helps.

[Bob Phillips]

Post the actual database.

[lifeson]

Or the code you are using to extract the data from the database

[sheena]

Hi Lifeson,

Here is the access file am using, thank you

[lifeson]

Hi Lifeson,

Here is the access file am using, thank you

Where?

If your trying to use excell to retrieve data from access as in your original post then I need to see what code you are using (from yor VB editor) in excel rather than the database itself

[Kenneth Hobs]

No attachment was added, try again.

[sheena]

Somehow I cant upload my access file and I dont know why, but anyway here is the VBA am using. My file is called Customers.mdb with a table called Year_20008 with one of the product, please help me from this agony, I have tried the code invain, thanks.

Sub ADODB()
' Set Reference in Tools to: Microsoft ActiveX Data Objects 2.x Library
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, Row As Long, s As String

' Database information
DBFullName = ActiveWorkbook.Path & "\Customers.mdb"
'Exit?
If Dir(DBFullName) = "" Then Exit Sub

'Clear any existing data from activesheet
Cells.Clear

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
' Record locking
Recordset.CursorType = adOpenKeyset
Recordset.LockType = adLockOptimistic

With Recordset
' Filter
'Src = "SELECT * FROM Products WHERE ProductName = 'Box of Staples' "
'Src = Src & "and P_ID = 30"
'Src = "SELECT Orders.P_ID, Orders.OrderDate " & _
"FROM Orders " & _
"WHERE (((Orders.OrderDate) " & _
"Between #12/01/2008# and #08/04/2008#))"
Src = "SELECT * FROM Year_2008"
Recordset.Open Source:=Src, ActiveConnection:=Connection

' Write the field names
For Col = 0 To .Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next Col

' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset

If .RecordCount < 1 Then GoTo endnow
.MoveFirst
For Row = 0 To (.RecordCount - 1)
'Debug.Print CStr(.Fields(Row).Value)
.MoveNext
Next Row
End With
endnow:
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

[sheena]

Hi Kenneth here is the code

[Kenneth Hobs]

Please use VBA code tags when you post VBA code. Click the VBA button and paste.

The code will work fine. Did you add the Microsoft ActiveX Data Objects 2.8 reference? Was customers.mdb located in the same path as the current workbook? If not, change:
[VBA]DBFullName = ActiveWorkbook.Path & "\Customers.mdb"[/VBA]
to your drive and path. e.g.
[VBA]DBFullName = "c:\yourpath\Customers.mdb"[/VBA]

Not sure why you had trouble attaching customers.mdb since you were able to attach a file earlier. If the file is too big, I guess that could cause it. You can always post to a free shared site like 4shared.com, box.net or mediafire.com.

[sheena]

I still have a compile error in this line:

Dim Connection As ADODB.Connection

[Bob Phillips]

Have you set a reference in the VBIDE (Tools>References) to Microsoft ActiveX Data Objects library?

[lifeson]

You aren't telling the recordset where to put the records

[vba]Sub ADODB()
' Set Reference in Tools to: Microsoft ActiveX Data Objects 2.x Library
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, Row As Long, s As String
'declare a worksheet to use....
dim ws as worksheet

'set the worksheet you want to work with...
set ws = Thisworkbook.worksheets("YourSheetNameHere")

' Database information
DBFullName = ActiveWorkbook.Path & "\Customers.mdb"
'Exit?
If Dir(DBFullName) = "" Then Exit Sub

'Clear any existing data from activesheet
ws.Cells.Clear

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
' Record locking
Recordset.CursorType = adOpenKeyset
Recordset.LockType = adLockOptimistic

With Recordset
'build your query...

Src = "SELECT * FROM Year_2008"

'open the connection
Recordset.Open Source:=Src, ActiveConnection:=Connection

' Write the field names
'There is no worksheet specified...................................
'You aren't telling it where to write the records with just Range("A1")... use ws.range("A1")...


For Col = 0 To .Fields.Count - 1
ws.Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next Col

' Write the recordset
ws.Range("A1").Offset(1, 0).CopyFromRecordset Recordset

'you may be better using recordset.EOF here...
If .RecordCount < 1 Then GoTo endnow
.MoveFirst
For Row = 0 To (.RecordCount - 1)
'Debug.Print CStr(.Fields(Row).Value)
.MoveNext
Next Row
End With
endnow:
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub[/vba]

[Simon Lloyd]

Sheena/Caroll if you must crosspost (please see the link in my signature for an explanation) you must supply the links to the posts in the other forums, its only fair to the folk helping you!
http://www.excelforum.com/excel-prog...a-and-ado.html