export data from Excel to MS Access (ADO) using VBA

[Peter Brom]

I'm looking for an example VBA Code to transfer data from Excel to MS Access
2000. The issue is that the spreadsheet contains more then 100 hundred named
cells which needs to be transferred to the corresponding Field Name in MS
Access 2000. I'm not a beginner programmer but i don't want to code all the
separate named cells into MS Access so i'm looking for some smart code to do
this.

Does anyone know wehere i can find some example VBA code to accomplish this.

Thank you in advance

Peter Brom
(e-mail address removed)

 

[Guest]

Not just 100% sure exactly what you are looking for but here is a fairly
generic procedure to write to an Access Database.

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

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

On Error GoTo ErrorHandler
'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") = "Bob"
rst.Fields("Logged") = "In"
rst.Fields("Time") = Now()

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

ErrorHandler:
'Add error handling
End Sub

HTH

 

[Guest]

Have you tried using the Import Spreadsheet Wizard in Access? Depending on
how your data is structured in Excel, you may be able to import it without
programming at all.