Writting to tables in Access

[Catherine Benoit]

I need to write and read data in an MSAccess mdb from Excel. I've tried
different variations of the "OpenDatabase" syntax to no avail. Do you have
an example of code that will enable me to write and read data from MSAccess
tables?

Catherine

 

[Guest]

Here is some code for you. You just need to change the location of the
database, the table and the field names. This also requires a reference to
the ADO library which is added by doing the following:

In the checking in the VBA window Tools -> References -> Microsoft ADO ...

Private Const m_cDBLocation As String = "C:\Forecast.mdb"
Private Const m_cLogFile As String = "tblLogFile"

Public Sub LogIn()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & m_cDBLocation & ";"

rst.Open m_cLogFile, cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rst.AddNew

rst.Fields("User Name") = "Tada"
rst.Fields("In As") = "Shazam"
rst.Fields("Logged") = "Whoopee"
rst.Fields("Time") = Now()

rst.Update

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub

Hope this helps...

 

[Tom Ogilvy]

http://www.erlandsendata.no/english/index.php?t=envbadac