Appending or updating Access db with Excel??

[dfarland]

1st scenario: Have a row of data in an Excel workbook that I would like to append as a record to an Access DB.

2nd scenario: Have opened up an Access DB in Excel with DAO and have made changes to a record. Now I would like to update the Access db with those changes.

Is there any DAO functions that would write to a db?

Any help would be appreciated.

Dean

 

[Nikita6003]

Hi Dean,

you've got 3 options: either insert/update the record straight in the table using DAO objects, build some INSERT or UPDATE SQL, and run this using DAO objects, or call on a stored INSERT or UPDATE proc in your Access db, again using DAO objects

Examples:

Sub NewRecordToTable()
    Dim l_rsTable As Recordset
    Dim l_dbDatabase As Database
    Dim l_wsWorkSpace As Workspace
    
    Dim l_wkbWorkbook As Workbook
    Dim l_wksWorkSheet As Worksheet
    
    Set l_wkbWorkbook = ThisWorkbook
    Set l_wksWorkSheet = l_wkbWorkbook.Worksheets("NameOfSheet")
    
    Set l_wksWorkSpace = DBEngine.CreateWorkspaces("Test", "admin", "", dbUseJet)
    Set l_dbDatabase = l_wksWorkSpace.OpenDatabase(&quot;<FullPathAndFileName>&quot;)
    Set l_rsTable = l_dbDatabase.OpenRecordset(&quot;tblWhatever&quot;, dbOpenTable)
    
    'Add record to table
    l_rsTable.AddNew
    l_rsTable.Fields(&quot;FieldName1&quot;) = l_wksWorkSheet.Range(&quot;A1&quot;)
    l_rsTable.Fields(&quot;FieldName2&quot;) = l_wksWorkSheet.Range(&quot;B1&quot;)
    '...
    l_rsTable.Fields(&quot;FieldNameN&quot;) = l_wksWorkSheet.Range(&quot;N1&quot;)
    l_rsTable.Update
    
    l_rsTable.Close
    l_dbDatabase.Close
    l_wsWorkSpace.Close
    
    'Release objects
    Set l_rsTable = Nothing
    Set l_dbDatabase = Nothing
    Set l_wsWorkSpace = Nothing
    Set l_wkbWorksheet = Nothing
    Set l_wkbWorkbook = Nothing
End Sub

Method 2: create some SQL in Excel and Execute it using the Database object:

Sub NewRecordUsingSQL()
    Dim l_sSQL As String
    
    Dim l_dbDatabase As Database
    Dim l_wsWorkSpace As Workspace
    
    Dim l_wkbWorkbook As Workbook
    Dim l_wksWorkSheet As Worksheet
    
    Set l_wkbWorkbook = ThisWorkbook
    Set l_wksWorkSheet = l_wkbWorkbook.Worksheets("NameOfSheet")
    
    Set l_wksWorkSpace = DBEngine.CreateWorkspaces("Test", "admin", "", dbUseJet)
    Set l_dbDatabase = l_wksWorkSpace.OpenDatabase(&quot;<FullPathAndFileName>&quot;)
    
    l_sSQL = &quot;INSERT INTO tblTest ( Field1, Field2, Field3&quot; & vbLf
    l_sSQL = l_sSQL & &quot;Values &quot; & l_wksWorkSheet.Range(&quot;A1&quot;) & &quot;, &quot; & l_wksWorkSheet.Range(&quot;B1&quot;) & &quot;, &quot; & l_wksWorkSheet.Range(&quot;C1&quot;)
    
    'An INSERT is an action query, therefore needs to be invoked using EXECUTE
    'SELECTS are invoked using OPENRECORDSET
    'The dbFailOnError will force the db to rollback & return an error if the INSERT should fail
    l_dbDatabase.Execute l_sSQL, dbFailOnError
    
    l_dbDatabase.Close
    l_wsWorkSpace.Close
    
    'Release objects
    Set l_rsTable = Nothing
    Set l_dbDatabase = Nothing
    Set l_wsWorkSpace = Nothing
    Set l_wkbWorksheet = Nothing
    Set l_wkbWorkbook = Nothing
    
End Sub

Method 3: use the QueryDef object to call a stored procedure in Access:

Sub InvokeStoredProcInAccess()
    'NB: you'll have to set up the query in Access before you can use this!!
    'Goto Access, and add the SQL needed. I'm using a PARAMETER query so that I can assign the values I need to add to each parameter
    'The query in Access would be something like:
    '"PARAMETERS [@Field1] Text, [@Field2] Text, [@Field3] Double;" & vbLf
    '"INSERT INTO tblCtd ( Field1, Field2, Field3 )" & vbLf
    '"SELECT [@Field] AS Expr1, [@Field] AS Expr2, [@Field] AS Expr3;"
    'I saved the query with the name qryUpdateTable
    'Here's how to invoke:
    
    Dim l_qdfTmp As QueryDef
    Dim l_dbDatabase As Database
    Dim l_wsWorkSpace As Workspace
    
    Dim l_wkbWorkbook As Workbook
    Dim l_wksWorkSheet As Worksheet
    
    Set l_wkbWorkbook = ThisWorkbook
    Set l_wksWorkSheet = l_wkbWorkbook.Worksheets("NameOfSheet")
    
    Set l_wksWorkSpace = DBEngine.CreateWorkspaces("Test", "admin", "", dbUseJet)
    Set l_dbDatabase = l_wksWorkSpace.OpenDatabase(&quot;<FullPathAndFileName>&quot;)
    Set l_qdfTmp = l_dbDatabase.QueryDefs(&quot;qryUpdateTable&quot;)
    
    'Assign the values from the workbook to the parameters
    l_qdfTmp.Parameters(&quot;@Field1&quot;) = l_wksWorkSheet.Range(&quot;A1&quot;)
    l_qdfTmp.Parameters(&quot;@Field2&quot;) = l_wksWorkSheet.Range(&quot;B1&quot;)
    l_qdfTmp.Parameters(&quot;@Field3&quot;) = l_wksWorkSheet.Range(&quot;C1&quot;)
    
    'Run the query. Again, it's an action query so use EXECUTE
    l_qdfTmp.Execute dbFailOnError
    
    'Close & release objects
    l_dbDatabase.Close
    l_wsWorkSpace.Close
    
    'Release objects
    l_qdfTmp.Close
    l_dbDatabase.Close
    l_wsWorkSpace.Close
    
    Set l_qdfTmp = Nothing
    Set l_dbDatabase = Nothing
    Set l_wsWorkSpace = Nothing
    Set l_wkbWorksheet = Nothing
    Set l_wkbWorkbook = Nothing
    
End Sub

If you're updating an existing record:
For method one: Find the record you need to update, use the

Edit

method to allow you to change any of the field values, and use

Update

to save your changes
For methods 2/3: use an UPDATE query rather than an INSERT one

HTH


Cheers
Nikki
Look, mommy, I'm flying!