VBA code to run queries on specific tables (1 Viewer)

[moscgama]

Hi,

First, sorry if this is posted in the wrong thread! I couldn't decide where it actually goes.

Anyways, I am pretty new to VBA coding and need some help with running queries on specific tables. This is part of a multistep process, of importing data that needs to be transposed before appending to the "cleaned" database. First, users will save auto generated, Excel workbooks from a machine into a designated folder. I have code that will import these workbooks (an unknown number at a time) into Access in separate tables by workbook. Each table will have a similar name, but different extension (ie Sheet101, Sheet102, etc.). This is where the issue arises. The files are in long form, not wide. I have a series of queries (unfortunately, they're not SQL queries, but I can convert them to be) that transform the data from long to wide. However, I do not know how to go about writing code that will run the queries ONLY on the imported tables (again an unknown number of tables with similar names), not the rest of the tables in my database. I'm guessing it involves a do loop, but I am not positive.

I would really appreciate any advice you may have! Please let me know if you have any questions, or if I need to be more clear.

Thanks,

moscgama

 

[TJPoorman]

This code will loop through all the tables and the if statement would fire your code only where the table name is like "Sheet*":

Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
    If tdf.Name Like "Sheet*" Then
        'Run your code here
    End If
Next

 

[moscgama]

Hi TJPoorman,

Thanks so much for your help (and sorry for the almost two week delay)! It works!! However, I am stuck again. I have tried to figure out how to remove the reference to the first table I loop through (I hard coded it) and loop through all of the other tables. (I am basically trying to run a query on the subset of tables I reference in the previous if then statement. Again, I am not positive on the number of actual tables on which the query will need to be run.)

SELECT tblSheet11.[ID], tblSheet11.Name, tblSheet11.Address, tblSheet11.[DOB_1], tblSheet11.M_I INTO dedup2
FROM tblSheet11
WHERE (((tblSheet11.DOB_1) Is Null))
ORDER BY tblSheet11.Name;

I tried in the FROM statement to use LIKE similar to the code TJPoorman suggested above, but I have not been able to find an example quite like mine (I will freely admit I’m a terrible Google searcher).
Does anyone have any suggestions? Please let me know if I’m being unclear!!


Thanks in advance for the help!

 

[TJPoorman]

Can you post your current code?

 

[smig]

why don't you put all data in the same table and a field that will let you know which file it was came from ?

 

[moscgama]

Hi,
Thank you both for your help!
Smig, I tried to create one table that combined all of the tables into one, but some of the information is duplicated when adding the tables together. The issues arises because I need these individual records for later data processing.
For the first step of the data processing, I need to create a table to remove all of the duplicate values from the original table. I currently have a “Make Table” query with the following code:

SELECT tblSheet11.[ID], tblSheet11.Name, tblSheet11.Address, tblSheet11.[DOB_1], tblSheet11.MI INTO dedup
FROM tblSheet1
WHERE (((tblSheet11.DOB_1) Is Null))
ORDER BY tblSheet11.ID;

I need the value of the From table to change according to the which table the program is currently processing through. I was thinking of using DoCmd.OpenQuery to open the “Make Table” query. After looking again through my data processing steps, this is the only query whose table names need to change. I have tried the following code with no luck:

SELECT tblSheet11.[ID], tblSheet11.Name, tblSheet11.Address, tblSheet11.[DOB_1], tblSheet11.MI INTO dedup
FROM Like “tblsheet*”
WHERE (((tblSheet11.DOB_1) Is Null))
ORDER BY tblSheet11.ID;

Please let me know if there’s a way of changing the “FROM” statement so that it will take the random table values! Also, please let me know if you have other ideas.
Thanks again for your advice and time!

 

[TJPoorman]

Would this work?

Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
    If tdf.Name Like "Sheet*" Then
        CurrentDb.Execute "SELECT [ID], [Name], [Address], [DOB_1], [MI] INTO dedup" & _
            " FROM [" & tdf.Name & "]" & _
            " WHERE ((([DOB_1]) Is Null))" & _
            " ORDER BY [ID];"
    End If
Next

 

[moscgama]

Hi,

Thanks TJPoorMan for the response! I've finally have gotten back to this project and have tried to get the code to work for a few days with no luck. I keep getting the Error:

Run-time Error '91': Object variable or With block variable not set

I believe the error is coming in at the FROM statement:

" FROM [" & tdf.Name & "]" & _

As always, any advice would be much appreciated! Thanks in advance for the help!!

Best,
Moscgama

 

[moscgama]

Hi,
Just to finalize this post, I was able to find code on another forum that solved my final problem. The code is as follows:

Public Function loop()
Dim StrHold As String
Dim strsql As String
strHold = "SELECT [ID], [Name], [Address], [450], [DOB_1] INTO Dedup FROM "
With CurrentDb
For Each tdf In .TableDefs
If tdf.Name Like "tblSheet*" Then
strsql = strHold & tdf.Name & ";"
Debug.Print strHold
.Execute strsql
End if
Next tdf
End with
End function

Thanks for everyone’s help!
Best,
Moscgama