Pardon me i get so frustrated sometimes

G

George Hester

I have a SQL statement which is an append query. Looks like this:

strApndKH_LH = "INSERT INTO [" & strTblNameKeyed & "] " & _
"SELECT [" & strTblNameLinked & "].* " & _
"FROM [" & strTblNameLinked & "];"

This I execute witrh:

dB.Execute strApndKH_LH, dbFailOnError

where Dim db as DAO.Database.

Then right below this db.Execute statement I have:

Call MakeTableFromSQL1(newTblName, strDbName, strDbPath, strFldName1, strFldName2, strTblNameKeyed)

This MakeTableFromSQL1 all it does is order strTblNameKeyed on one field and give me a new table newTblName.. No primary keys involved here.

This succeeds and fails about 50% of the time. When it fails I get "Could not update: Error #3260 currently locked by user 'Admin' on machine 'MyMachine." This is occurring at the point where the MakeTableQuery is started in the sub above.

It looks to be a problem of Locking. I tried FreeLock and everything came crashing down. Can't use that. Any ideas how I can free the locks after the first statement so the second one can succeed without this error? Thanks.
 
M

[MVP] S. Clark

It sounds like the first execute isn't finished, before the next on starts.
I don't know the code behind MakeTableFromSQL1, but maybe there is something
you can do to ensure that the first query is completed prior to starting the
next.

For i = 1 to 5000
DoEvents
Next

Also, unless it is absolutely neccessary, I would not use a make table.
Instead, use a combination of a delete query and an append query. Make
tables are pretty resource intensive, so if you can avoid them, do.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I have a SQL statement which is an append query. Looks like this:

strApndKH_LH = "INSERT INTO [" & strTblNameKeyed & "] " & _
"SELECT [" & strTblNameLinked & "].* " & _
"FROM [" & strTblNameLinked & "];"

This I execute witrh:

dB.Execute strApndKH_LH, dbFailOnError

where Dim db as DAO.Database.

Then right below this db.Execute statement I have:

Call MakeTableFromSQL1(newTblName, strDbName, strDbPath,
strFldName1, strFldName2, strTblNameKeyed)

This MakeTableFromSQL1 all it does is order strTblNameKeyed on one field and
give me a new table newTblName.. No primary keys involved here.

This succeeds and fails about 50% of the time. When it fails I get "Could
not update: Error #3260 currently locked by user 'Admin' on machine
'MyMachine." This is occurring at the point where the MakeTableQuery is
started in the sub above.

It looks to be a problem of Locking. I tried FreeLock and everything came
crashing down. Can't use that. Any ideas how I can free the locks after
the first statement so the second one can succeed without this error?
Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top