Excel VBA : Import data from Access Database

[ajay_1151]

Hi, I am using this code to Transfer data from Excel to Access 2003 Database.
But I am not able to write a code to import data from Access Database. Can someone please help me, please please please?

//
Sub TransferDataFromExcelToAccessDB()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection

dbpath = ThisWorkbook.Path & "\My_Database.mdb"
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & dbpath & "; Jet OLEDB:Database Password= 2121;"

cn.Open
Set rs = New ADODB.Recordset
rs.Open "Raw_Data", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew
.Fields("MyDate") = Sheet1.Range("A2")
.Fields("Item_Name") = Sheet1.Range("B2")
.Fields("Import_Date") = Sheet1.Range("C2")
.Fields("Export_Date") = Sheet1.Range("D2")
.Update
End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
//

 

[Nebu]

Hi:

The data you want to import, is it the output of any query you run in access? If yes design the query in access as per the data you need and establish a connection manually between access and excel (its just a one-time exercise). After that write the code to run this query from excel without opening the access, this way you can achieve the desired results without transferring data from access item-by-item.

Thanks

 

[ajay_1151]

Hi Nebu, thanks for your reply. I am actually new to VBA. I have developed above code by reading multiple articles, but unaware of most of the concepts. I am actually trying to extract the data from the Table (Raw_Data). I did not create any query for that. But just now I have created a query (myQRY) which has 4 columns [MyDate, Item_Name, Import_Date, Export_Date]. Can you please please please write a code for me which will extract the data on to a new excel workbook with Field headings? Thank you very very much for your help in advance

 

[Nebu]

Sub RunAccessActionQuery()

Dim cnn, rst As Object
Dim strConn, strTargetDB, strQry As String
Dim lngRecs As Long
    strTargetDB = "Your data base name"
    strQry = Sheet11.Range("A1" ).Text  'Give your query name in square brackets in one cell in excel (A cell in the sheet where the table will not overwrite ) the macro will pick the query name from here to run in MS Access in the background
    Set cnn = CreateObject("ADODB.Connection")
  
    strConn = ThisWorkbook.Path & "\" & "Your DB name"
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open strConn
    End With
    Set rst = CreateObject("ADODB.Recordset")
    Set rst = cnn.Execute(strQry, lngRecs)
  
    Debug.Print lngRecs
    cnn.Close
  
    Set cnn = Nothing
   ThisWorkbook.RefreshAll
    MsgBox "Done!!!", vbExclamation, "Status"
End Sub

Try this code I am successfully using this to run around 9 queries in a loop . I understand you have only one query so I got rid off the loop

Thanks

 

[ajay_1151]

Sub RunAccessActionQuery()

Dim cnn, rst As Object
Dim strConn, strTargetDB, strQry As String
Dim lngRecs As Long
    strTargetDB = "Your data base name"
    strQry = Sheet11.Range("A1" ).Text  'Give your query name in square brackets in one cell in excel (A cell in the sheet where the table will not overwrite ) the macro will pick the query name from here to run in MS Access in the background
    Set cnn = CreateObject("ADODB.Connection")
 
    strConn = ThisWorkbook.Path & "\" & "Your DB name"
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open strConn
    End With
    Set rst = CreateObject("ADODB.Recordset")
    Set rst = cnn.Execute(strQry, lngRecs)
 
    Debug.Print lngRecs
    cnn.Close
 
    Set cnn = Nothing
   ThisWorkbook.RefreshAll
    MsgBox "Done!!!", vbExclamation, "Status"
End Sub

Try this code I am successfully using this to run around 9 queries in a loop . I understand you have only one query so I got rid off the loop

Thanks

Hi, thank you so very much for the quick response I have tried this code, but it is giving me some error, also I am not sure where to incorporate the database password "1234" so that It would open the database accordingly. For your review I am attaching the Tool along with the database. Please help.

 

[Nebu]

Hi:

Please find the code for importing data into Excel

Private Sub CommandButton2_Click()
'Import data from Access Database to New Excel Workbook
Dim cnn, rst As Object
Dim strTargetDB, strQry As String
Dim lngRecs As Long
    strTargetDB = "Raw_Data"
    strQry = "[Raw_Data_Query]"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your file path\Raw_Database.mdb;Persist Security Info=False;Jet OLEDB:Database Password=1234"
    Set rst = CreateObject("ADODB.Recordset")
    Set rst = cnn.Execute(strQry, lngRecs)
    Debug.Print lngRecs
    cnn.Close
    Set cnn = Nothing
    ThisWorkbook.RefreshAll
    MsgBox "Done!!!", vbExclamation, "Status"
End Sub

You will have create a newworkbook , place this macro in that workbook and establish a manual connection(this is a one time exercise) before running this macro. Follow the steps in this link to establish connection
https://support.office.com/en-us/ar...workbook-a3d6500c-4bec-40ce-8cdf-fb4edb723525

Once the connection is established the access query will run in the background with out opening the access db with the code I have given above.

For exporting the data to access, if the data is standard you can link the raw data file to the access db, you do not have to write a macro to do this, Once you open the access DB right click and select link tables from the menu and follow the wizard. Once the link is established whatever changes you make to your excel file will be automatically reflected in your access DB. I cannot do this for you because the link is system/network specific , whatever link I establish here would not work at your end.

I have successfully run the above macro to refresh the query without opening your access DB.

Thanks

 

[ajay_1151]

Hi:

Please find the code for importing data into Excel

Private Sub CommandButton2_Click()
'Import data from Access Database to New Excel Workbook
Dim cnn, rst As Object
Dim strTargetDB, strQry As String
Dim lngRecs As Long
    strTargetDB = "Raw_Data"
    strQry = "[Raw_Data_Query]"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your file path\Raw_Database.mdb;Persist Security Info=False;Jet OLEDB:Database Password=1234"
    Set rst = CreateObject("ADODB.Recordset")
    Set rst = cnn.Execute(strQry, lngRecs)
    Debug.Print lngRecs
    cnn.Close
    Set cnn = Nothing
    ThisWorkbook.RefreshAll
    MsgBox "Done!!!", vbExclamation, "Status"
End Sub

You will have create a newworkbook , place this macro in that workbook and establish a manual connection(this is a one time exercise) before running this macro. Follow the steps in this link to establish connection
https://support.office.com/en-us/ar...workbook-a3d6500c-4bec-40ce-8cdf-fb4edb723525

Once the connection is established the access query will run in the background with out opening the access db with the code I have given above.

For exporting the data to access, if the data is standard you can link the raw data file to the access db, you do not have to write a macro to do this, Once you open the access DB right click and select link tables from the menu and follow the wizard. Once the link is established whatever changes you make to your excel file will be automatically reflected in your access DB. I cannot do this for you because the link is system/network specific , whatever link I establish here would not work at your end.

I have successfully run the above macro to refresh the query without opening your access DB.

Thanks

Thank you so very much Nebu for your quick and valuable help

 

[edwards142]

Friends,

I am feeling glad to share information regarding how to Import Excel Spreadsheet Data From Access Database. Try the steps mentioned in this post you will be easily able to import excel data into access without taking help of anyone. It’s really very helpful….! Just have a look.

 

[AlanSidman]

@edwards142
This posting is over 2 1/2 years old. I doubt that the OP is still concerned. You may wish to direct your efforts to helping those with more recent posts.