Pulling data from Access to Excel using VBA

[CLCoop]

Ugh what am I doing wrong: Using VBA code within Excel trying to pull in a TABLE from Access called H:\FAD.accdb table called UpdatedSTATErawdata all data into Workbook called: SOF onto the spreadsheet called: UpdatedRAWSTATE..... this will then update a pivot chart cause Access no longer does pivot charts! Here is the code I'm trying:

Sub UPDATE()
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "H:\FAD.accdb"

Set Connection = New ADODB.Connection
Connect = "Provider = Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ":"
Connection.Open ConnectionString:=Connect "Keep getting an error on this line"

Set Recordset = New ADODB.Recordset
With Recordset

Source = "SELECT * FROM UpdatedSTATErawdata WHERE [Grand_Total] = 'Grand Total'"

.Open Source:=Source, ActiveConnection:=Connection

'Write 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

ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Conection.Close
Set Connection = Nothing

End Sub

 

[bbotzong]

Did you add the library to the project? In the Visual Basic Environment (<alt>+<f11>), click Tools, References, then find Microsoft ActiveX Data Objects 2.8 Library. Put a check next to it.

Also, see a few minor changes as below... Notice I took out the colon after DBFullName & ":" to DBFullName & "". Then, try your code...

Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ""
Connection.Open Connect '"Keep getting an error on this line"

Hope that helps...

Bill B.

 

[CLCoop]

Thank you that worked great!!! I did your recommendations and we are good to go.. guess it helps to have a 2nd pair of eyes on code! I had turned on the Microsoft ActiveX Data Objects but only at 2.0 level

Final code used:
Sub UPDATE()
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

DBFullName = "H:\FAD.accdb"

Set Connection = New ADODB.Connection
Connect = "Provider = Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data Source=" & DBFullName & ""
Connection.Open ConnectionString:=Connect

Set Recordset = New ADODB.Recordset
With Recordset

Source = "SELECT * FROM UpdatedSTATErawdata WHERE [Grand_Total] = 'Grand Total'"

.Open Source:=Source, ActiveConnection:=Connection

'Write 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

ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub