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/...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.

Please Login or Register  to view this content.

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.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"keithl816" <[email protected]> wrote
in message news:[email protected]...
>
> Hello everybody,
>
> I hope someone can help me with this problem, I got the code below from
> this website.
>
> http://www.erlandsendata.no/english/...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
>
>
> --
> keithl816
> ------------------------------------------------------------------------
> keithl816's Profile:
http://www.excelforum.com/member.php...o&userid=21287
> View this thread: http://www.excelforum.com/showthread...hreadid=472278
>

[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