Access 2003, SQL Server and UPDATE (A Short Story)

Khaos

Okay, here's the deal:

Developing some software at work, I have need to connect to our SQL Server (Which stores all the information for our accounting / order entry systems) and retrieve information as well as update some fields.

All of this is tying in to an app that I am writing in Access 2003 VBA and VB6.

Thus far, the retrieving part is going quite well. I can SELECT all the information I need, display it... No problem. The problems arise when I try to UPDATE. Here is the code I am working with:

Dim mySQL As String, sFixed As String * 40
       
    
    Dim wrkMacola As Workspace, conMacola As Connection, rstOrderInfo As Recordset
    
    If Not txtJobNumber.Value <> "" Then Exit Sub
    
    Set wrkMacola = CreateWorkspace("MacolaWorkspace", "", "", dbUseODBC)
    Set conMacola = wrkMacola.OpenConnection("", dbDriverPrompt, False, "ODBC;DATABASE=DATA_02;DSN=Macola;APP=Microsoft Access 2003")
    
    mySQL = "SELECT * FROM OEORDHDR_SQL WHERE ord_no LIKE '%" & Trim(txtJobNumber.Value) & "'"
    Set rstOrderInfo = conMacola.OpenRecordset(mySQL)
    
    MsgBox rstOrderInfo.Fields("ord_no")
    
    sFixed = txtBillToName.Value
    
    mySQL = "UPDATE OEORDHDR_SQL SET "
    mySQL = mySQL & "bill_to_name = '" & sFixed & "' "
    mySQL = mySQL & "WHERE ord_no LIKE '%" & Trim(txtJobNumber.Value) & "'"
    
    conMacola.Execute mySQL

Note a few things here. First, the code used to use the actual-length string rather than a fixed length. The fixed length string was simply an attempt to make it work, since the field's maximum size is 40 characters. In addition, the only reason for the SELECT/MsgBox is to test the connection. It works, always has. Also, I have tried using dbDriverComplete and putting username/login in the connect string, as well as putting the WSID and Trust_Connection=Yes in the connect string, among other things. So far as I can see, the method of connection has nothing to do with it as the result is always the same: Successful connection, but unable to UPDATE.

The specific error I am getting is "Run-time error 3146. ODBC--call failed." with no help available. Haven't been able to find anything online about it that is applicable to my situation.

So, the question becomes:

Has anyone here successfully executed UPDATE commands from Access 2003 over an ODBC connection to SQL Server?

If so, did you run into any problems, and if you did run into problems, how did you work around them?

Does anyone see something obviously wrong with my code that I am missing?

Can anyone recommend an alternate method for UPDATING under these circumstances?

Does anyone know how to trap/retrieve SQL Server errors in VB so that I can hopefully get an error message that is more descriptive? I know that SQL Server is generating a more descriptive error; I just don't know how to view it.

Khaos

This is what the current SQL looks like:

UPDATE OEORDHDR_SQL SET bill_to_name = 'REXBURG CHAMBER OF COMMERCE             ' WHERE ord_no LIKE '%198617'

and this also does not work:

UPDATE OEORDHDR_SQL SET bill_to_name = 'REXBURG CHAMBER OF COMMERCE' WHERE ord_no LIKE '%198617'

As you guys can see, the WHERE clause is working fine as is for SELECT statements, which eliminates that as the problem. The UPDATE syntax is very straightforward and should be correct.

I have tried using UPDATE IGNORE, but I am not sure that SQL Server (2000 I think) supports it. In any event, UPDATE IGNORE did not work either.

primesuspect

This may be way off base, so pardon my ignorance, but are you sure the un/pw you're using has write permissions to the db?

Khaos

That's what we're working on... Ordinarily, any trusted connection can write to the database. All of our other software, most of which was developed by a contracting agency, connects in this manner.

We switched to ADODB from DAO, which is providing us with more detailed error messages. Again, the SELECT code is working but the UPDATE code is not. The error message we now get is "UPDATE permission denied on object (TABLENAME), database (DBASENAME), owner (OWNERNAME)."

We're trying various users, but keep getting the same error. Very strange considering all users can write to this table under normal circumstances. Currently working on setting up a new user with unrestricted permissions to test.

Edit: So, to sum it up, we're now using ADODB with the SQLOLEDB.1 provider.

Khaos

Got it working, turns out it was a problem with "Integrated Security=SSPI" in the connection string, which we eventually determined through pure trial and error. ><

primesuspect

Nothing like "screw around till it works"

Khaos

The universal cure... One reason why I hate doing tech support!

Five minutes of expert "screwing around" is good for about five HOURS of instructed "screwing around".

BLuKnight

... I suppose it's too late to convince you that using Access 2003 as your database is a bad idea...

I can feel my nightmares returning.

Khaos

If it's any consolation, I've developed extensively in Access (Not something I'm necessarily proud of) in the past and am well aware of its strengths and limitations. What we're undertaking is practically a case study on what makes Access useful.

One of my first Access projects some 4 years ago, which was to write complex aging & summary reports off a 20GB database, was a case study in what makes Access suck ass. Data intensive apps fall flat on their face. Apps that are not data intensive and require lots of user interaction work out well in Access.