Running queries with VBA functions in external database

[Sergey Poberezovskiy]

Hi,

I have an (update) query that utilizes my Custom VBA
function (BitAnd). I need to run this query from within
another database. The code (attached below) runs without
errors, but records do not update.
On another hand, when I step thru the code line by line,
everything seems to work just fine.
Could anyone please help?!!

The code:

Dim oAccess As Access.Application
Set oAccess = New Access.Application
With oAccess
.OpenCurrentDatabase vsDatabase
.DoCmd.OpenQuery "qryUpdateTempScores"
.CloseCurrentDatabase
End With

Any help is greatly appreciated.

 

[david epsom dot com dot au]

Is it just closing too quickly? Try using RunSQL
with UseTransaction = true, instead of OpenQuery.

(david)

 

[Sergey Poberezovskiy]

When I execute
oAccess.DoCmd.RunQuery "myUpdateQuery"
access fails with the following Error:
"Invalid SQL Statement.
Expected 'Delete', 'Insert', 'Procedure', 'Select'
or 'Update'."

Any other suggestions?...

 

[Douglas J. Steele]

What does the query do? From its name, I would have expected it to be an
Update query. If all it does is a select, then you can't use RunSQL.

 

[david epsom dot com dot au]

Yes, I would expect 'Update' in an update query :~)
What is your SQL?

(david)

 

[Sergey Poberezovskiy]

David,

The query uses three or four different pre-saved
subqueries and as part of one of them my BitAnd VBA
function that returns the result for bitwise comparison of
two Long Integers.
And "myUpdateQuery" is the name for an update query, not
the SQL. As I mentioned in previous post,
oAccess.DoCmd.OpenQuery "myUpdateQuery", dbFailOnError
does not fail.

Sergey.

 

[Sergey Poberezovskiy]

Let me explain the whole task, as this is just a part of a
bigger problem:

I have a few databases that are older versions of a
current application (with (sometimes) different tables and
queries structure. Most of the info presented to their
clients is calculated on the fly. The customer has a
requirement to re-produce some info (on ad-hoc basis) as
it was sent to the clients at the time.

I came up with an idea to create a table within each
database to hold IDs (tblTempScores) to produce the info
and have "myUpdateQuery" that will update this table with
the required info in every database.

Then the code within current database will loop through
the IDs requested by the customer, pick the database that
holds the IDs, repopulate that database tblTempScores
table and run the update query. After that just re-import
the table data back into current database and produce the
report.

So my code for every old database is as follows:

1. RemDb.Execute "Delete * From tblTempScores"
2. Close RemDb
3. CurrentDb.Execute "Insert Into tblTempScores In 'remDb'
Select * From tblTempScores Where Condition"
4. CurrenDb.Execute "Delete * From tblTempScores Where
Condition"
5. Open RemDb,
oRemAccess.DoCmd.OpenQuery "qryUpdateTempScores"
6. Close RemDb
7. CurrentDb.Execute "Insert Into tblTempScores Select *
From tblTempScores In 'remDb'"

SQL strings are all fine, I just indicated them here to
save typing. And as I mentioned before, step-by-step
execution produces the desired result.

I have found that even though step 3 inserts records into
remDb (by checking CurrentDb.RecordsAffected), when I get
to step 5, "Select count(*) from tblTempScores" returns 0,
and therefore there is nothing to update
for "myUpdateQuery". The strange thing is that after step
6 if I open remDb through interface, the table contains
the records (not updated).

So my problem has shifted somehow, but still needs
resolving.

Would greatly appreciate any suggestions.

Sergey

 

[david epsom dot com dot au]

Sorry, I just realised that I gave incomplete/misleading instructions.

1) OpenQuery has not updated your records, so it has failed in a way.

2) This is a method to TEST to see what the problem is.

3) Replace OpenQuery with RunSQL, using an explicit transaction.
You will have to put the SQL into the RunSQL command, it won't accept
a query name.

4) The explicit transaction will prevent Access from closing until after
the query is completed.

5) If this is the problem, then the update will succeed.

6) If this is the problem, then you will have to decide on a solution.

7) If not, we will have to look more to find the problem.

(david)