Using Access as an ODBC Database

Question

This is what I'm trying to do: I need to consolidate weekly forecasting excel templates from many different groups at work. I have created a form for them to fill out with all of their forecasting items / variances to plan / explanations. At that point I have a button on the excel template that places all of the data into a regular uniform table line by line beside the form. What I'd like to happen is after everything is in the uniform excel table that they click another button and using an ODBC connection to my access database make it append the data to a table in access for me? I have heard of this being done before but I'm not sure how.

I start by trying in excel by going to "From Other Sources" under the Data tab and clicking "From Microsoft Query" and then going through the steps of selecting my access database as the data source and then going through those steps until I get to a button I click that says "View data or edit query in Microsoft Query" but then I get lost how to use that to append data from the excel sheet to the access database using the ODBC connection.

Could anyone please help me figure out how to do this? There will be multiple groups appending to this access database from the excel template. Access will be an easy way for me to keep track of all of the data if I can do this.

Thanks!

Answer 1

Use an ADO connection (see connectionstrings.com). The steps are:

1. Set a reference in your project to Microsoft ActiveX Data Objects 6.1 Library (or whatever version you have).

2. Dim cn As ADODB.Connection

3. Open the connection (a Google search will show you how)

4. Load the values in your Excel sheet into an array

5. Loop through the array and insert each record into the table. For example:

cn.Execute "INSERT INTO SomeTable VALUES (" & array(i,1) & "," & array(i,2) &...

The above is just a guide and may not be exactly right syntactically.

Edit:

Dim conString as String

conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Persist Security Info=False;"

Set cn = New ADODB.Connection cn.Open conString

Answer 2

Here's the answer to the problem I had above with the help of the other people responding on here:

    Sub ADOFromExcelToAccess()
   ' exports data from the active worksheet to a table in an Access database
 ' this procedure must be edited before use
  Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=Path to the database;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Forecast_Items", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("Q" & r).Formula) > 0
' repeat until first empty cell in column A
    With rs
        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("UserName") = Range("O" & r).Value
        .Fields("Forecast_Date") = Range("P" & r).Value
        .Fields("Area") = Range("Q" & r).Value
        .Fields("Description_Item") = Range("R" & r).Value
        .Fields("Account") = Range("S" & r).Value
        .Fields("RRDD") = Range("T" & r).Value
        .Fields("CostCenter") = Range("U" & r).Value
        .Fields("Fleet") = Range("V" & r).Value
        .Fields("ForecastAmount") = Range("W" & r).Value
        .Fields("PlanAmount") = Range("X" & r).Value
        .Fields("VarianceForecast") = Range("Y" & r).Value
        .Fields("Explanation") = Range("Z" & r).Value

        ' add more fields if necessary...
        .Update ' stores the new record
    End With
    r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing




    End Sub

The other code that I have that is supposed to batch all or none if there are errors is. However when an error does occur it is still writing the ones that went through successfully.

     Sub ADOFromExcelToAccess()

     If MsgBox("This Button Will Submit all Data in the Table to the Right & Clear the Table! Are you sure?", vbYesNo) = vbNo Then Exit Sub


     ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=Filepath.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Forecast_Items", cn, adOpenKeyset, adLockBatchOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("Q" & r).Formula) > 0
' repeat until first empty cell in column A
    With rs
        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("UserName") = Range("O" & r).Value
        .Fields("Forecast_Date") = Range("P" & r).Value
        .Fields("Area") = Range("Q" & r).Value
        .Fields("Description_Item") = Range("R" & r).Value
        .Fields("Account") = Range("S" & r).Value
        .Fields("RRDD") = Range("T" & r).Value
        .Fields("CostCenter") = Range("U" & r).Value
        .Fields("Fleet") = Range("V" & r).Value
        .Fields("ForecastAmount") = Range("W" & r).Value
        .Fields("PlanAmount") = Range("X" & r).Value
        .Fields("VarianceForecast") = Range("Y" & r).Value
        .Fields("Explanation") = Range("Z" & r).Value



        ' add more fields if necessary...

    End With
    r = r + 1 ' next row
Loop
rs.UpdateBatch 'injects full table from excel into access at the same time, eliminating possible errors with inserting certain rows over others
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

MsgBox ("Data was Submitted Successfully!")

Exit Sub




  End Sub