Run a query from a file

[xfp39024]

I would like to know if it is possible (and, if so, how) to run a query in MS Access where the query lives in a file outside of the mdb. e.g. Say I have:

foo.mdb


bar.sql

I would like to open foo.mdb and run bar.sql from the MS-Access session.

Can this be done?

If so, how?

[JoeM]

I have a lot of Access databases that link to SQL queries.
I simply create an ODBC connection that would link me to the SQL database, and then create a Pass-Through query in Access to that SQL database to run a query from it and return the results to Access.

See this link on Pass-Through queries: http://support.microsoft.com/kb/303968

[xfp39024]

OK -- but that's not the question. I have a query sitting in the same folder as the mdb, not on SQL Server. I want to execute that query from within MS-Access. Can that be done?

[JoeM]

I have a query sitting in the same folder as the mdb

What exactly do you mean?
What kind of query is it?
What is it connected to?
How do you run it (outside of Access)?

[xfp39024]

1. It is an MS-Access SELECT query
2. It is not (by itself) connected to anything
3. I cannot run it outside of Access.

WHat I would like to do is say, "Hey, Access! I have this query sitting on disk in the file C:\Temp\MyQuery.sql. Would you please run it for me in the current context? That is, would you treat it as if it was actually in the database I have open?"

[JoeM]

Can you explain why you want to do this in this manner? Especially since a Select query does not update anything?
What are you going to be using this query for?

If we have a clearer picutre as to what you are trying to accomplish (and why and how), we may be able to provide alternatives.

[xfp39024]

Can you explain why you want to do this in this manner? Especially since a Select query does not update anything?
What are you going to be using this query for?

Two motivators:

1. I would like to be able to test out new query ideas without modifying the Access db, other than having a generic "run foo.sql" in the mdb. Note that those test queries will not always be run against the MS-Access database (though sometimes they will). Frequently they will be passed-through to other DBMSes (Not just SQL Server). So I would have "RUn Oracle", "Run Sql Server", "Run DB2" etc. in MS Access.

2. I want my data and queries to get a divorce. A prime motivator is that I want to keep my queries under version control (e.g. Subverion, Git). In general, VCS's handle flat files, not database content (except for a few 3rd-party products). FWIW I use the Generate Scripts wizard in SQL Server to get the DDL and DML out of the database and into flat files that I then keep under version control.

Note that I can certainly use PowerShell, VB.net or C# to run queries (stored in the filesystem) against Access or other DBMSes. That's easy. What I'm curious about is whether this is possible directly using built-in MS-Access functionality.

[JoeM]

I do not know if you can run Access queries from something other than Access (never really heard of anyone trying). You may want to look into possibly using MS Query. I have not used that myself, but I believe it may have the ability to do that.

Note also that Access databases can link to other Access databases. So you could make a separate Access database for testing purposes which links into your main Acess databases for your testing (really, you can have an unlimited amount of different front-end databases attached to a back-end database). So, this option would not modify your main Access database at all, like you wanted.

[Dunro]

I would look into reading data from a text file into a string variable and then creating a querydef based on the text file.

The latter would look like this:

const TEMP_QUERY_NAME = "temporary query" ' constant name to give temporary query
currentdb.CreateQueryDef Name:=TEMP_QUERY_NAME SQLText:=stringVariable ' creates temporary query

docmd.openquery TEMP_QUERY_NAME ' opens query to view
currentdb.QueryDefs.Delete TEMP_QUERY_NAME ' deletes temporary query

[xfp39024]

I do not know if you can run Access queries from something other than Access (never really heard of anyone trying). You may want to look into possibly using MS Query. I have not used that myself, but I believe it may have the ability to do that.

Note also that Access databases can link to other Access databases. So you could make a separate Access database for testing purposes which links into your main Acess databases for your testing (really, you can have an unlimited amount of different front-end databases attached to a back-end database). So, this option would not modify your main Access database at all, like you wanted.

Yes, that is true, though it would not get my queries into flat files suitable for version control systems.

[JoeM]

I guess it all depends on which version control systems you are using and how you use them.
We use Tortoise SVN, and include Access databases. The process is manual though. Don't know if you have or are using a more automated system.

It looks like Dunro might have have a solution for you to run the query code contained in a flat-file. Might want to look into that.

[xfp39024]

I use Tortoise SVN as well. Note though, that Tortoise will treat an mdb as a binary file. That means that you can't track changes to individual queries or macros or table definitions etc. within the mdb. That is at least, without extracting those from the mdb to flat files. (Unless there's a Tortoise plugin for that? I'm not aware of any)

[JoeM]

We just starting using Tortoise SVN not too long ago.

Are you saying that if I commit an Access database, then make some changes to it (let's say to some of the queries), and then commit that, if I wanted to roll back to the original version, it wouldn't do that correctly?

[xfp39024]

No, that's not what I'm saying. YOu can certainly roll back any changes you make, but you cannot see what the changes are! Suppose you have a query in that db called "q1". You add the db to SVN via Tortoise and carry on. Later that month you modify q1 and commit the change. What I am saying is that you can't then do a TortoiseSVN Diff on the mdb and have it show your changes to q1. That's because Tortoise (well, Subversion, actually) stores the mdb as a binary file.

I want to be able to revert changes, like you. BUT, I also want to see what changes were made...line by line. That's what you can't do if you put the whole mdb under version control.

[JoeM]

Ah, I see. We don't use SVN like that. Usually, if I have any important queries where I want to keep different versions of my code, I will just save it off to a text file and commit that.