Executing SQL Statement in VBA Code within Microsoft Access Project 1

[jcg6]

Hi,

I am having some issues with executing SQL commands within my VBA code for a form. These commands worked fine when my tables were in Microsoft Access, but sine I have upgraded them into SQL Server - the commands do not work.

Now that I am working with a Microsoft Access Project instead of a Microsoft Access Database - these issues have presented themselves.

Here is an example of my SQL code that used to work:

Dim strSQL as String

strSQL = strSQL & "INSERT INTO Salvage SELECT * FROM Hardware WHERE MachineID =""" & Me![MachineID] & """"

db.Execute strSQL

This worked fine before I upgraded my tables. Now when I try and execute this I get an error message that states the following:

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

Can anyone offer any solutions to this dilema? I would really appreicate any assistance. Thanks very much.

 

[cmmrfrds]

Can you show the rest of your code surrounding the connection. For example, how is db defined and other connection variables.

 

[jcg6]

Thanks for responding. Here is the rest of my code for this particular statement. I don't have any connection strings, and maybe that is where my problem lies? I didn't have to open any connections when I was using Access, but maybe I need to now that my tables are connected via SQL. Any assistance you could offer would be great! Thanks

If I need to completely rethink this, that would be fine too. I'm fairly new at this, so I may be off track.


Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = strSQL & "INSERT INTO Salvage SELECT * FROM Hardware WHERE MachineID =""" & Me![MachineID] & """"

db.Execute strSQL

 

[RobertT687]

Simply change you command to:
db.Execute SqlStr, dbSeeChanges
I'm guessing that when you migrated the table to SQL Server you changed an Autonumber column type to IDENTITY.

 

[jcg6]

Thanks very much. You were exactly right!! I really appreciate your assistance.

 

[cmmrfrds]

Dim db As Database
Set db = CurrentDb

What you have is a mix of libraries. CurrentDb is part of the Application Library. Database is part of the DAO Library and an Access Project defaults to the ADODB library. It is probably best to get in the habit of using the ADODB library for data manipulation. The DAO and ADODB library have some of the same objects so it is also best to explicitly define the objects. For example, the execute method is in both the DAO and ADODB library. This is probably causing the problem on your execute statement.

Example
rs as DAO.RecordSet
rs as ADODB.RecordSet
'- Do a right click on the above objects and check the libraries.

Try this is place of what you have done.
Dim cn as new ADODB.Connection, rs as new ADODB.RecordSet

Set cn = ActiveProject.Connection
cn.Execute strSQL
'- you should not need the additional option of dbSeeChanges
'- Also, if you are retrieving a recordset it is better to use the Open Method of the Recordset object - example
rs.Open strSQL, cn, 3, 3

'- The execute method is best used for updates or inserts