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