docmd.runsql VS. DB.Execute method

[glgcag]

I have inherited a database that I am converting to a SQL Server 2005 backend from the JET database. The previous programmer created a function for action queries that he passes a sql string into and then uses the db.execute method.

Function RunSql(sql As String, Optional plOptions As Long) As Boolean
On error goto ErrRoutine

Screen.Mousepointer = 11
GetDB.Execute sql, plOptions 'GetDB is a function to get current database
RunSql = True
etc.

Unfortunately, it doesn't seem to work with linked SQL Server tables for some reason. Any idea why it won't work? Or should I just replace the GetDB.Execute sql, plOptions with:

Docmd.SetWarnings False
Docmd.RunSQL(sql)
Docmd.SetWarnings True

I don't get the plOptions that the .execute method allows, but I've searched the whole database and the previous programmer doesn't actually ever pass any options in through the function anyway, so I don't think I'm missing anything.

Any cautions about my replacement code?

 

[RustyAfro]

I wasn't aware the .execute had a limitation on linked tables, but if the docmd.runsql works, I do not see a downside to it. I have used both and never noticed a performance difference (though if you have an ODBCdirect workspace, .execute gives you several additional options).

Docmd.runsql does have a "UseTransaction" argument that you can pass a false too if you don't need to worry about committing changes. It may help performance a bit (but I haven't noticed it if it does).

 

[glgcag]

I don't know for sure if it's a limitation, but the sql wouldn't execute so I assumed it was so.

I will test it thoroughly with the new code and see what happens!

 

[JerryKlmns]

I would suggest to call the function like

Dim SQLExecuted As Boolean
SQLExecuted = RunSql(sql, 128) 'dbFailOnError
...

because

In a Microsoft Jet workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.

The other thing to check is, from QDE run the same sql statement and see what happens.

 

[glgcag]

JerryKlmns,

I always check my sql strings in QDE when they fail, and this worked fine. Since in this case I am running a delete on a linked SQL Server 2005 table, I assumed there was a problem using the Execute method. I'll test further and let you know what I find.