Pardon me i get so frustrated sometimes

  • Thread starter Thread starter George Hester
  • Start date Start date
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.
 
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.
 
Back
Top