creating queries during runtime in msaccess

Question

hi, i have to create reports with certain data from an access database. i want to automate this process by the use of visual basic. i have created queries to achieve this but the problem is each time i have a different database(with same structure).

queries that i have has "create table", due to which i am unable to fire those queries from VB6 directly. Is there any way i can solve this problem?

Answer 1

You can run a CREATE TABLE SQL DDL statement (not a query!) against the Access database engine from VB6 by using a data access technology. ADO is best for DDL (e.g. richer syntax than DAO).

You can create a new table, including data, using

SELECT * INTO MyTableClone FROM MyTable;

but it will not copy any constraints e.g. all columns will be nullable :(

You can also use SQL DDL to create a VIEW or a PROCEDURE depending on what you mean by 'query'.

Answer 2

You can use the IN clause in SQL queries to reference different MDB files. For example:

"SELECT srpID, srpServiceRecordID, srpInvoiceDate, srpInvoiceNumber, srpParts " & _ 
"FROM ServiceRecordParts IN '" & strDatabasePathandName & "';"

Answer 3

You can run both queries and SQL from VBA. Here are a few notes.

Dim db As Database
Dim strSQL As String
Dim qdf As QueryDef

''Execute
Set db = CurrentDb
strSQL = "SELECT EmpID, EmpName INTO NewT FROM tblT WHERE EmpName Is Not Null"
''This will fail if the table already exists
''Only Action queries can be Executed
db.Execute strSQL, dbFailOnError
Debug.Print db.RecordsAffected

''This is not such a good approach
''Open query, will give warning
''that the table is about to be deleted.
DoCmd.OpenQuery "qryUpdate"

''Also not so good
''Open query, skip warning
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdate"
''This line is very important indeed
''never set warnings off, unless you
''set then on again
DoCmd.SetWarnings True

''Use query
Set qdf = db.QueryDefs("qryUpdate")
''The table in this SQL already exists, so
''a small diversion
db.Execute "DROP TABLE NewT", dbFailOnError
''Back on track
qdf.Execute dbFailOnError
Debug.Print qdf.RecordsAffected

''Change query SQL
qdf.SQL = strSQL

''Use SQL from query
strSQL = qdf.SQL
''The table in this SQL already exists, so
''a small diversion
db.Execute "DROP TABLE NewT", dbFailOnError
''Back on track
db.Execute strSQL, dbFailOnError
Debug.Print db.RecordsAffected

Your database will need a lot of compacting if you regularly add and delete tables and queries, so it is usually best to avoid this.