Make Table Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John D - 06 Mar 2007 17:26 GMT

I want a make table query to execute when a user opens a certain form. I have
the following code in the Form_Open Event. (Notes: "TW" Tables in my DB are
those created by Make Table queries.

___________________________
Private Sub Form_Open(Cancel As Integer)

' Create TW Table for Unique Years for PplOrg

'Variable to store SQL
Dim YrSQL As String

YrSQL = "SELECT TAbb_PplOrg.OrgID, TAbb_PplOrg.ForYear,
Count(TAbb_PplOrg.PersonID) AS CountOfPersonID " + _
"INTO TWc_PplOrgYrs " + _
"FROM TAbb_PplOrg " + _
"GROUP BY TAbb_PplOrg.OrgID, TAbb_PplOrg.ForYear;"

' Turn off warnings and execute

DoCmd.SetWarnings False
DoCmd.RunSQL YrSQL
DoCmd.SetWarnings True

End Sub
_______________________

When I attempt to open the Form I get "Run-time error '3211' ": ... could
not lock table 'TWc_PplOrgYrs' because it is already in use by another person
or process."

Anyone see what's wrong?

Thanks - John D

Reply to this Message

Douglas J. Steele - 06 Mar 2007 18:07 GMT

Does tWc_PplOrgYrs already exist?

If so, try:

YrSQL = "INSERT INTO TWc_PplOrgYrs " & _
"(OrgID, ForYear, CountOfPersonID) " & _
"SELECT TAbb_PplOrg.OrgID, TAbb_PplOrg.ForYear, " & _
"Count(TAbb_PplOrg.PersonID) AS CountOfPersonID " & _
"FROM TAbb_PplOrg " & _
"GROUP BY TAbb_PplOrg.OrgID, TAbb_PplOrg.ForYear;"

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I want a make table query to execute when a user opens a certain form. I
>have
[quoted text clipped - 33 lines]
>
> Thanks - John D

Reply to this Message

John D - 06 Mar 2007 20:44 GMT

Doug - thanks. It's getting there, but ...

Yes - the table is created every time the form is opened. However, the
"INSERT INTO..." technique APPENDS records to the existing table - which
creates lots of duplicates.

I put this statement into the procedure before the YrSQL string to try to
delete the previous table first:

DoCmd.DeleteObject acTable, "TWc_PplOrgYrs"

But now I get the same error message again with this line highlighted.

I want to at least delete all existing records in the table before INSERTING
new records - or else delete the existing table and write a new table. What
can I do?

Thanks - John D

> Does tWc_PplOrgYrs already exist?
>
[quoted text clipped - 44 lines]
> >
> > Thanks - John D

Reply to this Message

doyle60@aol.com - 06 Mar 2007 20:54 GMT

In my experiecen, stay away from Make Tables. It's usually better to
delete the data from an existing table and append the new data.

Also, if you have a split database, keep these tables on each users
front end, do not put on the server. If you are afraid about a lot of
data getting placed on a user's computer, you can always have it
deleted when they close the form.

Matt

Reply to this Message

Douglas J. Steele - 07 Mar 2007 12:55 GMT

To delete all of the data from the table, but not delete the table itself,
use:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM TWc_PplOrgYrs"
DoCmd.SetWarnings True

or (my preference)

CurrentDb.Execute "DELETE FROM TWc_PplOrgYrs", dbFailOnError

(The reason I prefer it is that you don't have set warnings on and off, and
it'll raise a trappable error if something goes wrong)

You can also use the Execute method to run your other SQL:

CurrentDb.Execute YrSQL, dbFailOnError

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Doug - thanks. It's getting there, but ...
>
[quoted text clipped - 66 lines]
>> >
>> > Thanks - John D

Reply to this Message

John D - 07 Mar 2007 15:36 GMT

Thanks Doug - and Matt

Works like a charm.

However, just to know, why was I getting the error message using the Make
Table approach? (Probably obvious - once you know.)

Thanks - John D

Reply to this Message

Douglas J. Steele - 07 Mar 2007 15:51 GMT

I can't really say without actually seeing your application (and no, I'm not
willing to look at it. <g>)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks Doug - and Matt
>
[quoted text clipped - 4 lines]
>
> Thanks - John D

Reply to this Message