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

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
[email protected]

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

"Peter Brom" wrote:

> 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
> [email protected]
>
>
>
>

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.

"Peter Brom" wrote:

> 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
> [email protected]
>
>
>
>