I have an excell sheet that we use to order a product. I would like to put a
button the sheet that runs code that will make a new record in an existing
table in a certain Access database. Some of the info will be from the cells
on the sheet and some will be standard text like "accessories for vehicle" &
range("a1") & "from XYZ company".
The existing database has a table called "Jobs" that this info will go into.
I want the code to select the very next autonumber/primary key (also used as
out job number) and send that number to a cell on the excel sheet.
Any one care to help me with this code?

"JCanyoneer" wrote in message
...
I have an excell sheet that we use to order a product. I would like to put
a
button the sheet that runs code that will make a new record in an existing
table in a certain Access database. Some of the info will be from the
cells
on the sheet and some will be standard text like "accessories for vehicle"
&
range("a1") & "from XYZ company".
The existing database has a table called "Jobs" that this info will go
into.
I want the code to select the very next autonumber/primary key (also used
as
out job number) and send that number to a cell on the excel sheet.
Any one care to help me with this code?


You can use ADO from a macro. Here's some sample code
http://www.exceltip.com/st/Import_da...Excel/427.html

/Fredrik

Fredrik Wahlgren wrote:
make a new record in an existing
table in a certain Access database. Some of the info will be from
the
cells
on the sheet and some will be standard text like "accessories for
vehicle"
&
range("a1") & "from XYZ company".
The existing database has a table called "Jobs" that this info will
go
into.
I want the code to select the very next autonumber/primary key
and send that number to a cell on the excel sheet.

You can use ADO from a macro. Here's some sample code
Import_data_from_Access_to_Excel_(ADO)_using_VBA_i n_Microsoft_Excel

The OP wants to *export*:

Sub TestExport()
Dim Con As Object
Dim rs As Object
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
.Open
.Execute _
"INSERT INTO Jobs (data_col) VALUES('" & _
"accessories for vehicle" & Range("A1").Value & _
"from XYZ company" & _
"');"
Set rs = .Execute( _
"SELECT @@IDENTITY")
Range("B1").CopyFromRecordset rs
End With
End Sub

Jamie.

--