Refresh an Access Query using VBA code in Excel

[Peter G]

I have linked data in an excel workbook from a tabel in an Access database
The table has been created from a Make tabel query in the same database. For
the data in the Access table to be up to date I need to run the Access query.
Can somone suggest a suitable VBA code for me to run in Excel that will
connect to the Access database and run (refresh) the query.

 

[Patrick Molloy]

set a reference to the Microsoft Active data Obkject 2.6 Library
(Tools/references)

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String

MyFile = "Risk.mdb"
SQL = "qsecurities" ' this is th ename of the query
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile
rst.Open SQL, con, adOpenStatic

 

[Peter G]

Set the reference to the Microsoft ActiveX data as you suggested, entered
the path and name of my mdb-file and also the name of the query. Ran the code
but no readcion in my Access database.

-Does the code you have suggested run the query in the backgound?
-The Access make table query requires some interaction confirming that data
is to be overwritten, a new fil will be created etc, does your code
automatically conform all the questions?
Is it necessary to "close down" Access after runnuing the query?

/PG

 

[Patrick Molloy]

Hi ... this doesn't open the access database as if you were using Microsift
Access. Its opens a connection to the database that allows to you to run TSQL
statements - and these can be the usual --- data retrieval, inserting amendig
etc, or commands that do things likeadding tables, or addign columns to
tables, and so on. It cannot be interactive as the database would normally be
in a server room and not in your own pc.
As it only opens a connection, there's no Access Application to close. Note
though that if you do have Access open with this database, then you might
inadvertently put a lock on the table if you have it open, Locks prevent two
or more users amendign the same record in a table. Locks also occur if, as
can easily happen with Access, the op edits a table. Another application,
like Excel, running sql against the database will not be able to read the
table while its being edited...and it will wait for euther a specified tome
out, or a default time out, before returning the code to the applicatios
control.

 

[Peter G]

This is a little strange! Thanks for your explanations regarding
interactivity, locking etc.
I have still not had any luck with the code. I have inserted a breakpoint in
the code and checked that the values of the variables (path, sql text) are
correct. I have tried running the code with Access running and also with it
closed down. Can you give me some suggestions how to try to trace what I am
doing incorrectly?

/PG

 

[Peter G]

Still trying to get this coed working and have arrived at the following,
which perhaps helps

1. The "Open.con" line needed a semi-colon at the end of code line.
2. If I replace the varialble sql with "SELECT Levfakt.Levnummer,
Levfakt.Fakturadat INTO AAA_Tabell FROM Levfakt" instead of the name of the
Access Query the code will actually create a table and fill it with data,
however the second time I run the code I get an error advising that the table
alread exists.

Any suggestions regarding the Query name issue?

/PG

 

[Peter G]

Further development... Problem not connecting to the query was probably the
name I had chosen for the query in Access. After Renaming the query the code
finds the query however I am getting an error message saying:

Incorrect SQL expression. DELETE, INSERT, PROCEDURE, SELECT or UPDATE
expected.

If I check teh SQL expression in the Access query, it is:

SELECT Levfakt.Levnummer, Levfakt.Fakturadat INTO AAA_Tabell
FROM Levfakt
WHERE (((Levfakt.Levnummer)="40516"));

Any sugggestions regarding the Error message?

/PG