Hi guys I have a complicated question which has been killing me for a while...Can you help me out. I cant run the Macro correctly with this error.
Here is the step by step process
1. I create a new database and link tables from a database internally. This table has 50 variables. Lets call this table A.
2. I import a table from a Daily updated database. This table has 45 rows. Lets call this Table B
3. Then I import 6 queries from prior weeks database to create this weeks report. 3 Make table queries (Query1, Query2, Query3) and 3 Append Queries (Queryappend 1, Queryappend 2, Queryappend 3).
Query 1 creates Table 1 (Query 1 is based off of table A and pulls 36 variables.)
Query 2 creates Table 2 and Query 3 creates Table 3(Query 2 and 3 are based off of table B and pulls the same 36 variables as above.)
Queryappend 1 creates Table 1+2+3 by inserting all the data from Table 1. This only pulls 35 variables from Table 1
Queryappend 2 appends data from table 2 to Table 1+2+3. This adds 31 records to the table
Queryappend 3 appends data from table 3 to Table 1+2+3. This adds 31 records to the table
The issue I have is after I import these tables, Query 1,2 and 3 run fine. For some reason Access changes Queryappend 1 by giving alias names Expr1, 2....36 to these variables. Do you think the issue is because Queryappend 2 and 3 dont have the same number of rows as table 1+2+3 to which they are appending data? Or is there another reason why access give alias name as Expr1...
If you guys can help me write a VBA code to do this that would be a lot helpful, cos VBA is faster than a Macro...I guess...Thanks for the help
Nope I still have the same issue. I think this is happening cos I am importing Queryappend 1,2,3 along with the query 1,2,3. The Queryappend's depend on the tables created by Query's. So I guess I'd have to bring them in manually.
Can anyone give me a VBA that runs 4 make table queries and 2 append queries? Thanks
you can run any query from VBA just by using
(Where you define the SQL string ahead of time). For a good tutorial on getting VBA and SQL to work together I'd suggest Martin Green's 6 part lecture. Else, if you're still having trouble could you post the SQLs for us to take a look at?