Basic Code to Open and Write a Record to Access

[Eldrod]

I'm using Excel and Access 2010 and have been searching for some simple code to open and write data from an excel spreadsheet appending to a table in Access. I have already successfully defined a connection via Excel's external data query to load the table into a sheet. However, at the end of my process, I need to add the new data I collected to the Access table. I have been searching for a general example of the code I need to make this work so I can tweak it.

Can anyone provide a simple code snippet to open an access database and append data to a table?

Any help is greatly appreciated!

Craig

 

[Trebor76]

Hi Craig,

Here's some old code I have to update a bank reconciliation database that I created some years ago that will give you the foundations you're after - just make sure to make a reference to Microsoft ActiveX Data Objects and to adjust the variables before running it:

Option Explicit
Sub ImportToAccess()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au

    '//Code needs a reference to Microsoft ActiveX Data Objects (Tools > References) controls//
    
    Dim strDBLocation As String, _
        strTableName As String, _
        strConnect As String
    Dim strMySQLStmt As String, _
        strWriteStmt As String
    Dim objMyDBConn As Object, _
        objMyRecSet As Object

    strDBLocation = "I:\Access\BankRec1.accdb" 'Full path of database. Change to suit.
    strTableName = "tblTest" 'Table name for records to be added. Change to suit.
    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBLocation & ";"
    
    Set objMyDBConn = New ADODB.Connection
    objMyDBConn.Open strConnect
    Set objMyRecSet = New ADODB.Recordset
    
    'Assumes the data for three fields in the 'tblTest' table to be updated (cheque number, cheque amount and date drawn) _
    are in A2, B2 and C2 (respectively). Change to suit.
    strMySQLStmt = Range("A2").Value & "', '"
    strMySQLStmt = strMySQLStmt & Range("B2").Value & "', '"
    strMySQLStmt = strMySQLStmt & Range("C2").Value
    
    'Creates a statement to write to the three desired fields. Change to suit.
    strWriteStmt = "INSERT INTO " & strTableName & "(ChqNoDrawn,DrawnAmt,DateDrawn) VALUES ('" & strMySQLStmt & "');"
    
    objMyDBConn.Execute strWriteStmt
        
    Set objMyDBConn = Nothing: Set objMyRecSet = Nothing

End Sub

Regards,

Robert

 

[Eldrod]

Thanks, Robert! I'll give this a try.

 

[Eldrod]

Making some progress, but now getting a "Must use an updateable Query" error on the execute statement. Searching for a solution, but so far the ones I have tried (modifying security on the database and its folder) have not led to a resolution. I'm running this script on a Windows 7 View machine, so I don't know if that has anything to do with the problem. Will try any reasonable suggestions!

 

[Trebor76]

Sorry I can't offer any direct advice as it works for me and my Access skills are somewhat rusty, though this thread
this thread may help where the spreadsheet data is imported while in Access from Excel not from Excel to Access.

 

[Eldrod]

I got most if the issues resolved last night. I'll document what I came across as it may help someone else later. Here's what cleared up issues with the script working correctly:

1. Discovered that the automatic connection query I set up with excel's wizard was accessing the database in Share Deny Write mode. I just went into the connection properties and changed that to Read. I also noticed later that there is a tab in the wizard that shows these access methods and defaults to Share Deny Write. Essentially, that means that process had exclusive write access, so I believe that is why I kept getting the "Must be an updateable query" message.

2. My Access table was two words separated by a space. Not sure if that really mattered, but I changed the space to an underscore.

3. One of my Access field names contained a "/" and another one contained a "&". The VBA script didn't seem to like that. Removed those from the field names and that problem was resolved.

4. Another Access field was named "Currency". VBA didn't like that so I updated it to "ISO_Currency" and that worked.

So, now I'm thinking that instead of using the External connection to load the whole database to Excel, I'll really rather run a query to return just the rows I need to excel. I created an Access Query that gives me what I need with a hard coded value to match a particular field, and am able to point to that query in the Excel Connection wizard, but I don't see how to pass parameters into that Access Query. I want to return only records that match the value of a Named Range variable.

If anyone has a code snip of how that works, I'd love to see it. Thanks!

Craig

 

[Eldrod]

I found this reference and this seems to work for what I need. Thanks!

Running an Access Parameter Query from Excel » Bacon Bits:

Craig