New record in table in Access from VBA in Excel

I have an excel file that that a customer uses to order product. I can have
up to 40 different files at on time. (eg. order1.xls,
order2.xls,..order40.xls) I have an Access database that I use to track jobs
at the shop that take the orders. I wouls like to place a button on the excel
sheet that when clicked would run code that would:

1. Open a new record in table "jobs" in acces.
2. Place information from the excel file into certain fields in the table.
(eg. order1.xls, cell C2=11252. I would like to place the following
info in field "description" in table "jobs" - "Vehicle# 11252"")
3. Place value from field "JobNumber" in table "jobs" into cell D1 on
order1.xls.

I have little experience with interapplication code. I copied and tried this
from a web page but it does not work:

Sub MakeJob()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\Documents_ and Settings\jeremy.CREEDBILT\My Documents\Test Job.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
With rs
.AddNew ' create a new record
' add values to each field in the record
.fields("Date") = Now()
If Range("a2") = "chevrolet" Then
.fields("Customer") = "Courtesy Chevrolet"
Else
.fields("Customer") = "Five Star Ford"
End If
.fields("Description") = "Accessories for Pick up, APS veh# " &
Range _("A1").Value
' add more fields if necessary...
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

This code is one I am using on a sample DB until I get the code figured
right. Cell references might be different. I get this error when I try to run
the code:

Compile error:
User-define type not defined.
and this part of the code is highlighted:
cn As ADODB.Connection

Can anyone help me with the code for this project?

Sounds like it should be a fun project... As for the user defined function
error you probably need to reference the project to ADO. In the VBE click on
Tools -> References -> Browse the list to find Microsoft ADO (some version
thereof). This should cure your UDF error.

HTH

"JCanyoneer" wrote:

> I have an excel file that that a customer uses to order product. I can have
> up to 40 different files at on time. (eg. order1.xls,
> order2.xls,..order40.xls) I have an Access database that I use to track jobs
> at the shop that take the orders. I wouls like to place a button on the excel
> sheet that when clicked would run code that would:
>
> 1. Open a new record in table "jobs" in acces.
> 2. Place information from the excel file into certain fields in the table.
> (eg. order1.xls, cell C2=11252. I would like to place the following
> info in field "description" in table "jobs" - "Vehicle# 11252"")
> 3. Place value from field "JobNumber" in table "jobs" into cell D1 on
> order1.xls.
>
> I have little experience with interapplication code. I copied and tried this
> from a web page but it does not work:
>
> Sub MakeJob()
> ' exports data from the active worksheet to a table in an Access database
> ' this procedure must be edited before use
> Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
> ' connect to the Access database
> Set cn = New ADODB.Connection
> cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
> Source=C:\Documents_ and Settings\jeremy.CREEDBILT\My Documents\Test Job.mdb;"
> ' open a recordset
> Set rs = New ADODB.Recordset
> rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
> ' all records in a table
> With rs
> .AddNew ' create a new record
> ' add values to each field in the record
> .fields("Date") = Now()
> If Range("a2") = "chevrolet" Then
> .fields("Customer") = "Courtesy Chevrolet"
> Else
> .fields("Customer") = "Five Star Ford"
> End If
> .fields("Description") = "Accessories for Pick up, APS veh# " &
> Range _("A1").Value
> ' add more fields if necessary...
> .Update ' stores the new record
> End With
> rs.Close
> Set rs = Nothing
> cn.Close
> Set cn = Nothing
> End Sub
>
> This code is one I am using on a sample DB until I get the code figured
> right. Cell references might be different. I get this error when I try to run
> the code:
>
> Compile error:
> User-define type not defined.
> and this part of the code is highlighted:
> cn As ADODB.Connection
>
> Can anyone help me with the code for this project?
>

Thank you, There is the ADO reference you told me to look for. I have
multiple versions of Microsoft ActiveX DAta Objects 2.x library references
available. Should I select all of them also?

"Jim Thomlinson" wrote:

> Sounds like it should be a fun project... As for the user defined function
> error you probably need to reference the project to ADO. In the VBE click on
> Tools -> References -> Browse the list to find Microsoft ADO (some version
> thereof). This should cure your UDF error.
>
> HTH
>
> "JCanyoneer" wrote:
>
> > I have an excel file that that a customer uses to order product. I can have
> > up to 40 different files at on time. (eg. order1.xls,
> > order2.xls,..order40.xls) I have an Access database that I use to track jobs
> > at the shop that take the orders. I wouls like to place a button on the excel
> > sheet that when clicked would run code that would:
> >
> > 1. Open a new record in table "jobs" in acces.
> > 2. Place information from the excel file into certain fields in the table.
> > (eg. order1.xls, cell C2=11252. I would like to place the following
> > info in field "description" in table "jobs" - "Vehicle# 11252"")
> > 3. Place value from field "JobNumber" in table "jobs" into cell D1 on
> > order1.xls.
> >
> > I have little experience with interapplication code. I copied and tried this
> > from a web page but it does not work:
> >
> > Sub MakeJob()
> > ' exports data from the active worksheet to a table in an Access database
> > ' this procedure must be edited before use
> > Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
> > ' connect to the Access database
> > Set cn = New ADODB.Connection
> > cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
> > Source=C:\Documents_ and Settings\jeremy.CREEDBILT\My Documents\Test Job.mdb;"
> > ' open a recordset
> > Set rs = New ADODB.Recordset
> > rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
> > ' all records in a table
> > With rs
> > .AddNew ' create a new record
> > ' add values to each field in the record
> > .fields("Date") = Now()
> > If Range("a2") = "chevrolet" Then
> > .fields("Customer") = "Courtesy Chevrolet"
> > Else
> > .fields("Customer") = "Five Star Ford"
> > End If
> > .fields("Description") = "Accessories for Pick up, APS veh# " &
> > Range _("A1").Value
> > ' add more fields if necessary...
> > .Update ' stores the new record
> > End With
> > rs.Close
> > Set rs = Nothing
> > cn.Close
> > Set cn = Nothing
> > End Sub
> >
> > This code is one I am using on a sample DB until I get the code figured
> > right. Cell references might be different. I get this error when I try to run
> > the code:
> >
> > Compile error:
> > User-define type not defined.
> > and this part of the code is highlighted:
> > cn As ADODB.Connection
> >
> > Can anyone help me with the code for this project?
> >

Select the most current one. If your users (assuming there are going to be
other users) have older versions of Excel, you may want to select an older
version of ADO to ensure backwards compatibility. That being said only select
one of them. Older versions will be less robust than newer versions.

HTH

"JCanyoneer" wrote:

> Thank you, There is the ADO reference you told me to look for. I have
> multiple versions of Microsoft ActiveX DAta Objects 2.x library references
> available. Should I select all of them also?
>
> "Jim Thomlinson" wrote:
>
> > Sounds like it should be a fun project... As for the user defined function
> > error you probably need to reference the project to ADO. In the VBE click on
> > Tools -> References -> Browse the list to find Microsoft ADO (some version
> > thereof). This should cure your UDF error.
> >
> > HTH
> >
> > "JCanyoneer" wrote:
> >
> > > I have an excel file that that a customer uses to order product. I can have
> > > up to 40 different files at on time. (eg. order1.xls,
> > > order2.xls,..order40.xls) I have an Access database that I use to track jobs
> > > at the shop that take the orders. I wouls like to place a button on the excel
> > > sheet that when clicked would run code that would:
> > >
> > > 1. Open a new record in table "jobs" in acces.
> > > 2. Place information from the excel file into certain fields in the table.
> > > (eg. order1.xls, cell C2=11252. I would like to place the following
> > > info in field "description" in table "jobs" - "Vehicle# 11252"")
> > > 3. Place value from field "JobNumber" in table "jobs" into cell D1 on
> > > order1.xls.
> > >
> > > I have little experience with interapplication code. I copied and tried this
> > > from a web page but it does not work:
> > >
> > > Sub MakeJob()
> > > ' exports data from the active worksheet to a table in an Access database
> > > ' this procedure must be edited before use
> > > Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
> > > ' connect to the Access database
> > > Set cn = New ADODB.Connection
> > > cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
> > > Source=C:\Documents_ and Settings\jeremy.CREEDBILT\My Documents\Test Job.mdb;"
> > > ' open a recordset
> > > Set rs = New ADODB.Recordset
> > > rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
> > > ' all records in a table
> > > With rs
> > > .AddNew ' create a new record
> > > ' add values to each field in the record
> > > .fields("Date") = Now()
> > > If Range("a2") = "chevrolet" Then
> > > .fields("Customer") = "Courtesy Chevrolet"
> > > Else
> > > .fields("Customer") = "Five Star Ford"
> > > End If
> > > .fields("Description") = "Accessories for Pick up, APS veh# " &
> > > Range _("A1").Value
> > > ' add more fields if necessary...
> > > .Update ' stores the new record
> > > End With
> > > rs.Close
> > > Set rs = Nothing
> > > cn.Close
> > > Set cn = Nothing
> > > End Sub
> > >
> > > This code is one I am using on a sample DB until I get the code figured
> > > right. Cell references might be different. I get this error when I try to run
> > > the code:
> > >
> > > Compile error:
> > > User-define type not defined.
> > > and this part of the code is highlighted:
> > > cn As ADODB.Connection
> > >
> > > Can anyone help me with the code for this project?
> > >

No. Select ADO 2.8 which is the latest (and possibly last) version of ADO.

/Fredrik

"JCanyoneer" <[email protected]> wrote in message
news:[email protected]...
> Thank you, There is the ADO reference you told me to look for. I have
> multiple versions of Microsoft ActiveX DAta Objects 2.x library references
> available. Should I select all of them also?
>


No. Select ADO 2.8 which is the latest (and possibly last) version of ADO.

/Fredrik

Jim Thomlinson wrote:
> If your users (assuming there are going to be
> other users) have older versions of Excel, you may want to select an
> older version of ADO to ensure backwards compatibility.

I think you are trying to apply advice you've heard about MS Office to
ADO. For example, if you set a reference to Excel9 (Excel2000) and open
it in ExcelXP on a machine without Excel2000, the reference will be
resolved to Excel10. The same does not apply to ADO. For example, if
you set a reference to ADO 2.1 and the machine only has ADO 2.8, the
result will be a missing reference. To handle this situation, use late
binding instead.

> Older versions will be less robust than newer versions.

This is not necessarily true. I've heard that MDAC 2.5 (still available
for download) is much more stable than the more recent MDAC 2.7. The
most recent version, MDAC 2.8 (also available to download), is AFAIK
considered stable.

Jamie.

--

Fredrik Wahlgren wrote:
> > I have
> > multiple versions of Microsoft ActiveX DAta Objects 2.x library
references
> > available. Should I select all of them also?
> >
>
> No. Select ADO 2.8 which is the latest (and possibly last) version of
ADO.

Again, this will result in a missing reference for users who do not
have ADO 2.8.

The better approach in these circumstances is to use late binding e.g.

Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

The version recordsed against "ADODB.Connection" in the registry will
be used. This will always be the most recent version of MDAC installed
on that machine i.e. will be ADO 2.8 if available.

Jamie.

--