Appending to Table using table Variable name

[Ginny2222]

Hi all,

I hope someone can help me!!

I want to import data on a monthly basis into tables, but I want to be able to use the same import buttons each month and pass the data into the relevant monthly table.

My Steps - Set Reporting Month variable (which sets the Table name Variable), choose the file to import, import this data to a holding table, append the data to the relevant monthly table.

I have this working up to the APPEND part. I have variables set up for the Table Objects, but I am having a problem using this variable in the APPEND SQL. I don't know how to do this!!!

Any help appreciated
tks
Ginny

 

[boblarson]

If you have designed this so you have a table for each month then you have designed this all incorrectly. You should use a NORMALIZED structure for a Relational Database. I would highly suggest reading this before going further:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

You should use Queries to be able to pull the applicable information for a particular month and therefore the data should reside in the same table (subject to normalization techniques, which could require the use of additional tables to represent the business model for the data accurately).

 

[Ginny2222]

Hi Bob,

There is a reason that the data has to be separated into each monthly table. The additional tables that would be needed cause as many problems if not more than keeping the data in 12 separate tables. The database is building a monthly trend for me. This is a bit complicated, but suffice to say that normalization in this case can't take place.

Is it possible to use a variable for a table name in an append query?

rgs
Ginny

 

[boblarson]

This is a bit complicated, but suffice to say that normalization in this case can't take place.

Actually, it is just that you can't see a way for it to happen (a very experienced database developer would have no problem with that). It actually CAN take place and should take place but you are currently limited in what you know how to do. But only you can do what you can do.

Is it possible to use a variable for a table name in an append query?
Yes, you can build a SQL string in code and run it.

A simplified example:

Dim strSQL As String
strSQL = "Select * FROM " & variableHere & " WHERE ID=XXXX"

 

[Ginny2222]

Hi Bob,

You are probably right, but as a professional company wasn't able to do it for us, I am trying a workaround here. I will try what you suggest above.

Thanks for advice.

rgs
Ginny