Hello Folks -
I have recently started a new job and they have no MS Access experience so there is nothing to go by here. I am needing code to pull data from query in Access DB in to my excel file. Secondly, I need to know what Tool References I need to check in order for this code to connect and work. I am using Excel 2013 and Access 2013. I have tried code from my previous job but it is crashing. I will put it below.
Thirdly, I don't know if the code would be different for a Query as opposed to a Table in Access as I am trying to pull from a query.
Thank you
Dave
I have recently started a new job and they have no MS Access experience so there is nothing to go by here. I am needing code to pull data from query in Access DB in to my excel file. Secondly, I need to know what Tool References I need to check in order for this code to connect and work. I am using Excel 2013 and Access 2013. I have tried code from my previous job but it is crashing. I will put it below.
Code:
Public Cn As New ADODB.Connection
Public Const varDefaultPathAccess = "C:\Users\dgrimm\Desktop\HR_Data.accdb"
Public xnn As New ADODB.Connection
Public Const varXNNProvType = "Provider=Microsoft.ACE.OLEDB.12.0"
Sub OpenCN()
If Cn.ConnectionString = "" Then
Cn.Open varXNNProvType & ";Data Source=" & varDefaultPathAccess
End If
End Sub
Sub get_table()
Application.ScreenUpdating = False
Dim i As Long
Dim rstTable As New ADODB.Recordset
Dim TableName As String
Dim VERSION_DATE As String
Sheets("Data").Select
TableName = "Retention_Table"
Range("A2:AZ100000").Select
Selection.ClearContents
OpenCN
rstTable.Open "SELECT * from " & TableName, Cn
Range("A1").CopyFromRecordset rstTable
Cn.Close
Set rstTable = Nothing
Set Cn = Nothing
'Sheets("Report").Select
'Range("A1").Select
End Sub
Thirdly, I don't know if the code would be different for a Query as opposed to a Table in Access as I am trying to pull from a query.
Thank you
Dave