Writing to an Access Database

[davetek]

Hi,

this is my first time attempting to write from Excel to an Access database..can anyone give me the basic syntax of writing data from a cell to a table.

Cheers

 

[Ted43]

Davetek,

I think you need to use Data Access Objects (DAO) to do this?

You need to include a reference to DAO in your Module (Tools/Reference then select Microsoft DAO 2.5/3.5 Compatibility Library)

The code below is from VBA Help and shows the use of the .Update command to change / add records to an Access database.

To add data from an Excel cell make "FirstName" & "LastName" variables and add the contents of the cell to the variables.

Hope this helps

This example demonstrates the Update method in conjunction with Edit method.

Sub UpdateX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees&quot

With rstEmployees
.Edit
' Store original data.
strOldFirst = !FirstName
strOldLast = !LastName
' Change data in edit buffer.
!FirstName = "Linda"
!LastName = "Kobara"

' Show contents of buffer and get user input.
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
!FirstName & " " & !LastName & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"

If MsgBox(strMessage, vbYesNo) = vbYes Then
.Update
Else
.CancelUpdate
End If

' Show the resulting data.
MsgBox "Data in recordset = " & !FirstName & " " & _
!LastName

' Restore original data because this is a demonstration.
If Not (strOldFirst = !FirstName And _
strOldLast = !LastName) Then
.Edit
!FirstName = strOldFirst
!LastName = strOldLast
.Update
End If

.Close
End With

dbsNorthwind.Close

End Sub

This example demonstrates the Update method in conjunction with the AddNew method.

Sub UpdateX2()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees&quot

With rstEmployees
.AddNew
!FirstName = "Bill"
!LastName = "Sornsin"

' Show contents of buffer and get user input.
strMessage = "AddNew in progress:" & vbCr & _

" Data in buffer = " & !FirstName & " " & _
!LastName & vbCr & vbCr & _
"Use Update to save buffer to recordset?"

If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
.Update
' Go to the new record and show the resulting data.
.Bookmark = .LastModified
MsgBox "Data in recordset = " & !FirstName & _
" " & !LastName
' Delete new data because this is a demonstration.
.Delete
Else
.CancelUpdate

MsgBox "No new record added."
End If

.Close
End With

dbsNorthwind.Close

End Sub