+ Reply to Thread
Results 1 to 2 of 2

Exporting to access but no error message

  1. #1
    Matt
    Guest

    Exporting to access but no error 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


  2. #2
    Tom Ogilvy
    Guest

    Re: Exporting to access but no error message

    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" <Matt@discussions.microsoft.com> wrote in message
    news:F9AB27DD-4A99-4271-BEDE-7ACA3A1EA1A8@microsoft.com...
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1