Basic Code to Open and Write a Record to Access

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
55
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,519
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:

Code:
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

Board Regular
Joined
Mar 11, 2010
Messages
55
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,519
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.
 
Last edited:

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
55
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
 

Forum statistics

Threads
1,089,438
Messages
5,408,221
Members
403,191
Latest member
fmstation

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top