I have created some code to export data from cells in excel to fields in
access. I attached the code to a button on a worksheet and it works fine.
Heres the code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnect As String, strSQL As String
Dim strJobNo As String

strJobNo = Cells(2, 2)
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Temp\temp.mdb;"
strSQL = "SELECT tblWIP.wpJobNo,tblWIP.wpJobCode, tblWIP.wpCost,
tblWIP.wpSales FROM tblWIP WHERE (((tblWIP.wpJobNo)= '" & strJobNo & "')); "

Set cnn = New ADODB.Connection
cnn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
'rs.AddNew
rs!wpSales = Cells(15, 3)
rs!wpCost = Cells(13, 2)
rs.Update
rs.Close
cnn.Close

The only time this does not work is when somebody is editing the particular
record in access, it just runs the code, does not update the database and
you get no errors. My question is what do I have to do to get an error
message in excel if somebody is editing the record in access.

Thanks In advance

Matt

You could test the file to see if you can get exlusive access to it (before
attempting the update)


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
I have created some code to export data from cells in excel to fields in
access. I attached the code to a button on a worksheet and it works fine.
Heres the code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnect As String, strSQL As String
Dim strJobNo As String

strJobNo = Cells(2, 2)
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Temp\temp.mdb;"
strSQL = "SELECT tblWIP.wpJobNo,tblWIP.wpJobCode, tblWIP.wpCost,
tblWIP.wpSales FROM tblWIP WHERE (((tblWIP.wpJobNo)= '" & strJobNo & "'));
"

Set cnn = New ADODB.Connection
cnn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
'rs.AddNew
rs!wpSales = Cells(15, 3)
rs!wpCost = Cells(13, 2)
rs.Update
rs.Close
cnn.Close

The only time this does not work is when somebody is editing the
particular
record in access, it just runs the code, does not update the database and
you get no errors. My question is what do I have to do to get an error
message in excel if somebody is editing the record in access.

Thanks In advance

Matt