Access table in SQL (Access 97/ SR2)

Topic

New Reply

Hi all,

I have the below VBA code which used to work perfectly. However, I have moved the access table to SQL and linked it and the code now does not work. The permissions for the table are all set up correctly, so it isn’t that.
My knowledge of VBA recordsets is a bit vague, therefore I was wondering if there is something wrong with the line: dbs.OpenRecordset(“OrderHeader”) and I might need to change something now the table is in SQL? Any help would be much appreciated.

Dim crs As Recordset
Set dbs = CurrentDb()
Set crs = dbs.OpenRecordset(“OrderHeader”)
With crs
.AddNew
!OrderNumber = 148386
!AccountId = 8
!OrderDate = Forms![Menu]![DateSelect]
!TeacherId = 1
!DocumentStatus = “P”
!InvoiceNumber = 180233
!InvoiceDate = Forms![Menu]![DateSelect]
crs.Update
End With

Reply | Quote

Replies

You indicate it doesn’t work but you don’t indicate the error you are getting, so this is a shot in the dark:

With SQL Server, you nearly always need to specify the type of recordset you are opening, and the option “cbSeeChanges” needs to be specified as a part of the OpenRecordset command. I presume you do have DAO specified.

Reply | Quote

Sorry WendellB, posted it in a bit of a rush (and no, I didnt rush to watch the football!).

The error returned was ODBC call failed, with no description of why it failed.

Appologies again, but i’m not entirely sure what you mean by: “I presume you do have DAO specified.”
I thought the .OpenRecordset part was the DAO? (Did mention that I wasn’t very experienced with recordsets didn’t I?).

Ill have a look at using dbseechanges, thanks for the tip.

Reply | Quote

Wendell may have missed the Access 97 version. In Access 2000 and later, DAO is only *one* of the object models, and both of them have recordset objects … *different* recordset objects. If you ever plan to upgrade , it might be a good idea to get into the practice of specifically declaring your objects as DAO.Database, DAO.Recordset, etc. That will make it easier to upgrade the databases to 2000 or 2002.

You might want to download and install the latest Jet service pack for Jet 3.5/Access 97. I think the SPs for that version went up to 4 or 5. That error in Access 2000 is certainly a cause for installing the latest Jet service pack. It might be the same in 97.

Reply | Quote

Thanks for the tip about the use of DAO. we have used that command in many databases and Its just dawned on me the scale of the task of upgrading. Which, to my knowledge, is the reason we haven’t upgraded yet.
I don’t suppose you could tell me if that error is likely to be caused by something i’ve done (or not) in Access, something i’ve done in SQL or the function literally isn’t available for use on an SQL table, hence the need for the Jet Service pack?

Reply | Quote

Sorry – I’ve been at clients since shortly after I posted the message. In any event, ODBC isn’t very informative about errors that occur on the SQL side of things – you typically get some sort of error message, sometimes with a number, that is meaningless from a debugging standpoint. Your code looks straightforward, so it seems unlikely that the problem is there, though I believe you do need to specify the recordset type as dynaset or something similar. (It’s been too long since I’ve actively worked with 97.) BTW, the cbSeeChanges in that post should be dbSeeChanges – but may not apply to 97. Given that doesn’t solve the problem, are you prehaps missing the reference to DAO altogether (my comment this morning attempted to suggest that, but not very well)? Another possibility is trying to store a text string in an integer or datetime column. Hope you are making progress on the problem.

Reply | Quote

I think i’ve finally worked out what the problem is.

I’m not 100% sure but I believe, when adding records through vba to an SQL table, the default value of a field doesnt get picked up. As the SQL table has a few fields that no not allow nulls, an odbc error is then returned as the field wasnt given a value and the default value didnt get automatically inserted.

Ill have to try and find the time to test it further just to be certain.

Reply | Quote

Did you create any default constraints on the SQL side? This is one area I had to manually add when I upgraded to SQL tables.

Reply | Quote

That will almost surely cause that kind of problem – we see it frequently. One solution is to set default values in SQL Server, the second is to set default values on a form, and the third is to allow null values for that field/column.

Reply | Quote