Executing Queries in VBA locks up DB

[martinmike2]

Hi, my code to execute the query is:

Private Sub cmdMerge_Click()
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "qryAppendDups"
End Sub

The query has no criteria, just appends from one table to another
allowing no duplicates.

Any help would be appreciated.

 

[Douglas J. Steele]

Private Sub cmdMerge_Click()
Dim db As DAO.Database

Set db = CurrentDb
db.QueryDefs("qryAppendDups").Execute

End Sub

 

[martinmike2]

Private Sub cmdMerge_Click()
Dim db As DAO.Database

  Set db = CurrentDb
  db.QueryDefs("qryAppendDups").Execute

End Sub

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









- Show quoted text -

not sure whats happening, I changed my code to match yours with the
same result. The application hangs and can only be closed by killing
the process.

 

[Klatuu]

Private Sub cmdMerge_Click()
Dim db As DAO.Database

Set db = CurrentDb
db.Execute "qryAppendDups", dbFailOnError

End Sub

As written, you will not know if an error occurs. The Execute method of
Currentdb does not go through the Access UI. It goes directly to Jet and
therefore will not report an error. The dbFailOnError will cause Jet to
report any errors to Access.

What happens if you run the query directly from the database window?

 

[martinmike2]

Private Sub cmdMerge_Click()
Dim db As DAO.Database

  Set db = CurrentDb
  db.Execute "qryAppendDups", dbFailOnError

End Sub

As written, you will not know if an error occurs.  The Execute method of
Currentdb does not go through the Access UI.  It goes directly to Jet and
therefore will not report an error.  The dbFailOnError will cause Jet to
report any errors to Access.

What happens if you run the query directly from the database window?

--
Dave Hargis, Microsoft Access MVP






- Show quoted text -

ok, when I run the query on its own i get all the usual warnings about
"cannot append 'somanyqueries' due to key violations". Since I am
appending to remove duplicates this is ok, and i just ok through it.

I tried your code and got "Error: 3022" because of the duplications
in the index. Is there a way to turn this off? Is it
docmd.setwarning false?

 

[Klatuu]

First, the Execute method is not affected by SetWarnings because it doesn't
go through the Access UI.
Turn it off?
That is what was happening before and why it appeared to hang up. The error
was occuring, but you were not seeing it.
If your query is attempting to append duplicates, then you need to fix the
query so it does not append duplicate values.

The easiest way to do that is with a subquery. For example, let's say the
only field in the table that is required to be unique is the primary key. So
using something like this, you can exclude records where the primary key
already exists:

WHERE NOT IN (SELECT RecID FROM DestinationTable);

 

[martinmike2]

First, the Execute method is not affected by SetWarnings because it doesn't
go through the Access UI.
Turn it off?
That is what was happening before and why it appeared to hang up.  The error
was occuring, but you were not seeing it.
If your query is attempting to append duplicates, then you need to fix the
query so it does not append duplicate values.

The easiest way to do that is with a subquery.  For example, let's say the
only field in the table that is required to be unique is the primary key. So
using something like this, you can exclude records where the primary key
already exists:

WHERE NOT IN (SELECT RecID FROM DestinationTable);

--
Dave Hargis, Microsoft Access MVP







- Show quoted text -

I got around it by using a single command button for each query.
Since each query was a step in a process it actually works out better,
making the end user OK each step.

But, ill give your example a try, thanks for the help Doug and Dave.