Hi guys please can someone help
I am trying to querying a access report via excel VBA
Normally in Access i enter start and end date (normally the same date)
then press run, and export it in pdf or excel, (R_UCITS-AIFM_Summary)
I am trying to do this just via excel, can someone help I am getting automation errors, I think the issue relates to there is a password on the database/I need to enter dates in my excel spreadsheet and refer this to VBA code, just unsure where to start,
Here is my code so far,
Sub getdatafromaccess()
Dim DBFullname As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recorder As ADODB.Recordset
Dim Col As Integer
Dim pword As String
Cells.Clear
pword = "Arch"
DBFullname = "Z:\Portfolio Analysis\Exposure
Monitoring\PAS_ACCESS.accdb"
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data source=" & DBFullname & ";" & "Jet OLEDB
atabase Password" = pword
Connection.Open ConnectionString:=Connect
Set Recordset = New ADODB.Recordset
With Recordset
Source = "Select * from R_UCITS-AIFM_Summary"
.Open Source = Source, Connection, ActiveConnection:=Connection
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
I am trying to querying a access report via excel VBA
Normally in Access i enter start and end date (normally the same date)
then press run, and export it in pdf or excel, (R_UCITS-AIFM_Summary)
I am trying to do this just via excel, can someone help I am getting automation errors, I think the issue relates to there is a password on the database/I need to enter dates in my excel spreadsheet and refer this to VBA code, just unsure where to start,
Here is my code so far,
Sub getdatafromaccess()
Dim DBFullname As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recorder As ADODB.Recordset
Dim Col As Integer
Dim pword As String
Cells.Clear
pword = "Arch"
DBFullname = "Z:\Portfolio Analysis\Exposure
Monitoring\PAS_ACCESS.accdb"
Set Connection = New ADODB.Connection
Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
Connect = Connect & "Data source=" & DBFullname & ";" & "Jet OLEDB

Connection.Open ConnectionString:=Connect
Set Recordset = New ADODB.Recordset
With Recordset
Source = "Select * from R_UCITS-AIFM_Summary"
.Open Source = Source, Connection, ActiveConnection:=Connection
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub