469,283 Members | 2,349 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,283 developers. It's quick & easy.

Programatically Create Query for tables

Thank you in advance for any and all assistance, it is GREATLY appreciated. I
was wondering if there is a way, with code to create a query for every table?
Or can you use a naming convention in tables and use a wildcard to create a
query for any table meeting that criteria?

I have over 100 tables and when I do updates, I've been having to create append
queries so that my clients don't loose data, but I would like to be able to
have code to create a query for each table type needed and not for the ones
that don't need updating or haven't changed since the last upgrade.

Thanks

Michael
Nov 13 '05 #1
8 5429
You can't do a SQL Query, but you can loop through the Tabledefs collection,
and find the table names meeting your criteria.

First, make sure you have a reference set to Microsoft DAO, then...

Public Function FilteredTableNameList( _
NamesLike As String _
) As VBA.Collection

Dim dbs As DAO.Database
Dim tdf As DAO.Tabledef
Dim strTableName As String
Dim colNames As VBA.Collection

Set colNames = New VBA.Collection
Set dbs = CurrentDb()
For Each tdf in dbs.Tabledefs
If strTableName Like NamesLike Then
colNames.Add strTableName
End If
Next

Set FilteredTableNameList = colNames
End Function

On 30 Jul 2004 16:41:41 GMT, lu*************@aol.com (Lumpierbritches) wrote:
Thank you in advance for any and all assistance, it is GREATLY appreciated. I
was wondering if there is a way, with code to create a query for every table?
Or can you use a naming convention in tables and use a wildcard to create a
query for any table meeting that criteria?

I have over 100 tables and when I do updates, I've been having to create append
queries so that my clients don't loose data, but I would like to be able to
have code to create a query for each table type needed and not for the ones
that don't need updating or haven't changed since the last upgrade.

Thanks

Michael


Nov 13 '05 #2
I'm not trying to sound dense, but how do I set the reference to DAO?
Nov 13 '05 #3
lu*************@aol.com (Lumpierbritches) wrote in message news:<20***************************@mb-m12.aol.com>...
I'm not trying to sound dense, but how do I set the reference to DAO?


Open any code window, go to Tools, References, and select Microsoft DAO 3.x...
Nov 13 '05 #4
Thank you for all your assistance, it's greatly appreciated.

I'm trying to programatically build queries in my program based on standard
table naming conventions of "tbl".

I have used the code so graciously provided in this newsgroup referencing DAO
3.5 in Access 97, however I get an error when running a Macro calling the code?

Here is the code I used:

Option Compare Database
Option Explicit

Public Function FilteredTableNameList(NamesLike As String) As VBA.Collection
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTableName As String
Dim colNames As VBA.Collection

Set colNames = New VBA.Collection
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If strTableName Like NamesLike Then
colNames.Add strTableName
End If
Next

Set FilteredTableNameList = colNames

End Function

The Error is:

The expression you entered has a function name that Microsoft Access can't
find.

All the tables are prefixed with "tbl" Please help!

Michael
Nov 13 '05 #5
Why not tell us which line had the error? Then we don't have to guess.

On 31 Jul 2004 10:09:21 GMT, lu*************@aol.com (Lumpierbritches) wrote:
Thank you for all your assistance, it's greatly appreciated.

I'm trying to programatically build queries in my program based on standard
table naming conventions of "tbl".

I have used the code so graciously provided in this newsgroup referencing DAO
3.5 in Access 97, however I get an error when running a Macro calling the code?

Here is the code I used:

Option Compare Database
Option Explicit

Public Function FilteredTableNameList(NamesLike As String) As VBA.Collection
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTableName As String
Dim colNames As VBA.Collection

Set colNames = New VBA.Collection
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If strTableName Like NamesLike Then
colNames.Add strTableName
End If
Next

Set FilteredTableNameList = colNames

End Function

The Error is:

The expression you entered has a function name that Microsoft Access can't
find.

All the tables are prefixed with "tbl" Please help!

Michael


Nov 13 '05 #6

Why not tell us which line had the error? I don't know which line generated
the error, I got it when running a Macro! That's why I'm asking for assistance.

Then we don't have to guess.

On 31 Jul 2004 10:09:21 GMT, lu*************@aol.com (Lumpierbritches) wrote:
Thank you for all your assistance, it's greatly appreciated.

I'm trying to programatically build queries in my program based on standard
table naming conventions of "tbl".

I have used the code so graciously provided in this newsgroup referencing DAO
3.5 in Access 97, however I get an error when running a Macro calling the code?
Here is the code I used:

Option Compare Database
Option Explicit

Public Function FilteredTableNameList(NamesLike As String) As VBA.Collection
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTableName As String
Dim colNames As VBA.Collection

Set colNames = New VBA.Collection
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If strTableName Like NamesLike Then
colNames.Add strTableName
End If
Next

Set FilteredTableNameList = colNames

End Function

The Error is:

The expression you entered has a function name that Microsoft Access can't
find.

All the tables are prefixed with "tbl" Please help!

Michael


Nov 13 '05 #7
When you get an error in VBA code, click the Debug option, and the code editor
will open up, sowing which line had the error. Try that, and see which line
had the problem.

On 01 Aug 2004 00:28:16 GMT, lu*************@aol.com (Lumpierbritches) wrote:

Why not tell us which line had the error? I don't know which line generated
the error, I got it when running a Macro! That's why I'm asking for assistance.

Then we don't have to guess.

On 31 Jul 2004 10:09:21 GMT, lu*************@aol.com (Lumpierbritches) wrote:
Thank you for all your assistance, it's greatly appreciated.

I'm trying to programatically build queries in my program based on standard
table naming conventions of "tbl".

I have used the code so graciously provided in this newsgroup referencing DAO
3.5 in Access 97, however I get an error when running a Macro calling the

code?

Here is the code I used:

Option Compare Database
Option Explicit

Public Function FilteredTableNameList(NamesLike As String) As VBA.Collection
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTableName As String
Dim colNames As VBA.Collection

Set colNames = New VBA.Collection
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If strTableName Like NamesLike Then
colNames.Add strTableName
End If
Next

Set FilteredTableNameList = colNames

End Function

The Error is:

The expression you entered has a function name that Microsoft Access can't
find.

All the tables are prefixed with "tbl" Please help!

Michael


Nov 13 '05 #8
Probably making things worse by posting this, but... and the sub
should probably have arguments that determine what tables get queries
built for them...

Option Compare Database
Option Explicit

Public Sub QueryForEachTable()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
'Set qdfTemp = .CreateQueryDef("", _
"SELECT * FROM Employees")

Set db = CurrentDb
For Each tdf In db.TableDefs
strSQL = ""
Set qdf = New QueryDef
If Left$(tdf.Name, 4) <> "MSys" Then
For Each fld In tdf.Fields
strSQL = strSQL & ", [" & fld.Name & "]"
Next fld
strSQL = Right$(strSQL, Len(strSQL) - 2)
strSQL = "SELECT " & strSQL & " FROM " & tdf.Name & ";"
Set qdf = db.CreateQueryDef("qry" & tdf.Name, strSQL)

End If
Next tdf
db.QueryDefs.Refresh
Set qdf = Nothing
Set tdf = Nothing
Set fld = Nothing
Set db = Nothing

End Sub

.... but it is a start.
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.