Make Table Query

[Guest]

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

 

[Douglas J. Steele]

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;"

 

[Guest]

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

 

[doyle60]

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

 

[Douglas J. Steele]

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

 

[Guest]

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

 

[Douglas J. Steele]

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