Hi everybody,

I am stuck trying to get access to retrieve data from excel using ado.
I don't know what i'm doing wrong and wish for someone to help me. I
created a test folder with and excel workbook named testbook and an
access db named db1 the table i'm trying to add info to is named
table1. The code I used is below. Can someone tell me what I've done
wrong?

The three field names in the access db is as follows:
field1 = Date
field2 = Name
field3 = Address

I have the same names in the excel spreadsheet.

I went into the VBE and clicked on Tools/References and moved Microsoft
Activex data objects 2.0 library just under the last box checked and I
checked this box. What do I click in the browse button to reference
access?


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:\testbook\db1.mdb;"
Set rs = New ADODB.Recordset
rs.Open "table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 2
Do While Len(Range("A" & r).Formula) 0
With rs
.AddNew
.Fields("Date") = Range("A" & r).Value
.Fields("Name") = Range("B" & r).Value
.Fields("Address") = Range("C" & r).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

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


I put this in a module, would that be the correct place to put this
code?

Any help would be deeply appreciated.

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Hi Larry,

Your code looked to me as though it would populate the Access table, and I
gave it a quick test, and it does.

When you ask, "What do I click in the browse button to reference access?" I
assume you mean the Browse button on the References - VBAProject dialog box.
You don't need to click that. Just click OK to dismiss the dialog box, and
run your code.

Yes, the code goes in a module, but a couple of phrases in your post make me
want to check a couple of points:

- The module containing your code is an Excel VBA module, not an Access
module.
- You intend to run the code from the Excel platform, sending data to Access
from Excel, rather than running the code in Access, retrieving from Excel.
The latter is of course entirely possible, but it's not how your code is
written.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"keithl816" wrote
in message ...

Hi everybody,

I am stuck trying to get access to retrieve data from excel using ado.
I don't know what i'm doing wrong and wish for someone to help me. I
created a test folder with and excel workbook named testbook and an
access db named db1 the table i'm trying to add info to is named
table1. The code I used is below. Can someone tell me what I've done
wrong?

The three field names in the access db is as follows:
field1 = Date
field2 = Name
field3 = Address

I have the same names in the excel spreadsheet.

I went into the VBE and clicked on Tools/References and moved Microsoft
Activex data objects 2.0 library just under the last box checked and I
checked this box. What do I click in the browse button to reference
access?


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:\testbook\db1.mdb;"
Set rs = New ADODB.Recordset
rs.Open "table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 2
Do While Len(Range("A" & r).Formula) 0
With rs
.AddNew
.Fields("Date") = Range("A" & r).Value
.Fields("Name") = Range("B" & r).Value
.Fields("Address") = Range("C" & r).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

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


I put this in a module, would that be the correct place to put this
code?

Any help would be deeply appreciated.

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile:
http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Hi Conrad,

Thanks for replying. Yes I am trying to transfer data from excel to
access. Is the code I'm using wrong? How can I run a code that will
transfer data to Access without manually importing the data everytime?

Again Thanks for repying,

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Hi again,

I figured out how to get the data to go to access, my path to the db
was wrong and the start row was incorrect, now the question is, do I
have to go into the mvb and click run/runsub/userform everytime or is
there a way to get that module to run automatically everytime I finish
entering data?

I've been trying to make this work for over two weeks and feel I am
very close to accomplishing what I'm trying to do. Every bit of help is
appreciated. and thanks to everybody that has replied.

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Hi Larry,

One way to get the code to run is to stick a button on the worksheet, and
associate the code with the button, so that when the user clicks the button
the code runs. But that's not fully automatic.

The other way is to use an event handler. Right-click the tab of the
worksheet where you're entering data and choose the View Code menu item.
You'll be escorted to the VBE with a new module. Use the dropdowns to choose
Worksheet, and Change. The Change event has a Target argument, representing
a cell. You can use that Target argument to check the cell's column
(Target.Column).

If it's reasonable to assume that the user has finished entering data when
the Target.Column equals, say, 3, run your code. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
ADOFromExcelToAccess
End If
End Sub

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"keithl816" wrote
in message ...

Hi again,

I figured out how to get the data to go to access, my path to the db
was wrong and the start row was incorrect, now the question is, do I
have to go into the mvb and click run/runsub/userform everytime or is
there a way to get that module to run automatically everytime I finish
entering data?

I've been trying to make this work for over two weeks and feel I am
very close to accomplishing what I'm trying to do. Every bit of help is
appreciated. and thanks to everybody that has replied.

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile:
http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Hi Conrad,

Thanks for replying back. Before I came back to check this site a
tinckered around with a command button on the sheet and got it to work
so I did what you stated in your reply. If I was to do the other option
through the worksheet tab wouldn't it create duplicates in the access
data table? The command button seems to be my best bet. The only time I
would need to click this button is at the end of the month. This also
would give me the opportunity to correct anything wrong before
transfering to access table.

Thanks again for replying Conrad. You have been very helpful and
informative.

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Hi Conrad,

I tried the code you supplied for the worksheet and it works great. Is
there a code that could be used to erase the data on the sheet after
closing the workbook? Or would it cause issues with the transfer of
data to access?

Thanks again,

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Conrad,

While Looking around in the forum I found a code that works great it
follows below:


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

Private Sub Workbook_Open()
Dim ToErase As Range, EraseRows As Integer

Set ToErase = Sheets("Sheet1").UsedRange
EraseRows = ToErase.Rows.Count - 1
If EraseRows 0 Then
Set ToErase = ToErase.Offset(1, 0).Range("1:" & EraseRows)
ToErase.ClearContents
End If

End Sub

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


Thanks for guiding me in the right direction.

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Is there a way to erase the contents of the table every time you upload from
excel? What I want to do is erase or overwrite the data in table1 everytime I
click the upload command button.

"Conrad Carlberg" wrote:

Hi Larry,

One way to get the code to run is to stick a button on the worksheet, and
associate the code with the button, so that when the user clicks the button
the code runs. But that's not fully automatic.

The other way is to use an event handler. Right-click the tab of the
worksheet where you're entering data and choose the View Code menu item.
You'll be escorted to the VBE with a new module. Use the dropdowns to choose
Worksheet, and Change. The Change event has a Target argument, representing
a cell. You can use that Target argument to check the cell's column
(Target.Column).

If it's reasonable to assume that the user has finished entering data when
the Target.Column equals, say, 3, run your code. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
ADOFromExcelToAccess
End If
End Sub

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"keithl816" wrote
in message ...

Hi again,

I figured out how to get the data to go to access, my path to the db
was wrong and the start row was incorrect, now the question is, do I
have to go into the mvb and click run/runsub/userform everytime or is
there a way to get that module to run automatically everytime I finish
entering data?

I've been trying to make this work for over two weeks and feel I am
very close to accomplishing what I'm trying to do. Every bit of help is
appreciated. and thanks to everybody that has replied.

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile:
http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422

Hi Secret Squirrel,

The code below is what I used. This code is placed in "This Workbook".
After you complete data entry, close the workbook and reopen it, it
should erase your data automatically. Change sheet name as needed. This
code doesn't need a button.


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

Private Sub Workbook_Open()
Dim ToErase As Range, EraseRows As Integer

Set ToErase = Sheets("Sheet1").UsedRange
EraseRows = ToErase.Rows.Count - 1
If EraseRows 0 Then
Set ToErase = ToErase.Offset(1, 0).Range("1:" & EraseRows)
ToErase.ClearContents
End If
End Sub

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


Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422