CODEWITHSUNDEEP
sqlms-accessvba
RazorKillBen
RazorKillBen

Reputation: 573

MS Access SQL IN Clause cannot write to database

I have recently re-written the VBA code in an inherited database, to include the IN statement to connect to the database, rather than have linked tables constantly connected (close to 400 users will have the front-end open at any one time, so it makes attempting to access the database to make design changes near on impossible).

However, reading from the database using the IN clause works just fine, but using them on a write statement seems to execute in VBA just fine, but not actually make any changes to the database.

The statement not writing is here:

CurrentDb.Execute " UPDATE [PS_DB] " & _
                " IN '' [MS Access;PWD=*passwd string here*;DATABASE=I:\*filepath here*\PM_DB_testenvironment.accdb] " & _
                " SET [PS_DeleteTS]= '" & Format(Now, "dd/mm/yy hh:mm") & " - " & usernm & "'" & _
                " WHERE PS_DB.PS_TeamName = '" & tm & "' AND [PS_Delete]= TRUE; "

Removing the IN clause, and executing the statement, writes to the database just fine.

Is it possible I need to set the read/write status of the database here?

Upvotes: 1

Views: 176

Answers (1)

Erik A
Erik A

Reputation: 32682

Using CurrentDb to execute queries on another Access database is a bad practice.

Open up a connection to that database instead:

Dim db As DAO.Database
Set db = DBEngine(0).OpenDatabase("I:\*filepath here*\PM_DB_testenvironment.accdb", False, False, "MS Access;PWD=*passwd string here*")
db.Execute "your query", dbFailOnError
db.Close

Upvotes: 3

Related Questions