Excel to access

[keithl816]

Hello everybody,

I hope someone can help me with this problem, I got the code below from
this website.

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

I'm trying to send data to an access table already created in a access
db. I followed the instructions on this site and still can't seem to
make it work. Where am I supposed to place this code? I tried right
clicking the sheet tab in excel and placing it in the sheet code that
the info is appearing on but it did not work. I also tried placing it
in a module. Still doesn't work.


Code:
--------------------

Sub ADOFromExcelToAccess()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\monthly report\boomaxedb.mdb;"
Set rs = New ADODB.Recordset
rs.Open "boomaxedb", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
.AddNew
.Fields("date") = Range("A" & r).Value
.Fields("operator") = Range("B" & r).Value
.Fields("name of road") = Range("C" & r).Value
.Fields("distance") = Range("d" & r).Value
.Update
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

--------------------


The data is stored in excel workbook named monthly report it is located
in a folder named Monthly, the access table is named boomaxedb. it is
located in the same folder.

I also clicked on the tools in vbe and reference and checked the box
next to microsoft activexdata objects 2.6 library.

Thanks in advance,

Larry

 

[Andy Wiggins]

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.

 

[keithl816]

Hi Andy,

Thanks for replying. I tried the example on the site you referred me
to. but I still cant get it to work. I have not used ado before so its
completely over my head. Am I missing something? What parts of the code
am I supposed to change to work in my workbook? I did go into the names
and defined pretty much everything that was in your example, changing
the names of the folders of course. I copied the codes in the modules
but am not sure what areas need to be changed to work for me.

Your help is extremely appreciated

Larry