Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    28

    Unanswered: Access VBA or Macro help

    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.)

    Then
    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

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Why not just force the naming in your query? That is, instead of
    SELECT tblName.fldName, ....
    do
    SELECT tblName.fldName AS [fldName]
    Me.Geek = True

  3. #3
    Join Date
    Feb 2009
    Posts
    28
    I did it for think Query Append 1, but I think I have to do it for 2 and 3 as well . Thanks for the quick response. I will let you know if this works

  4. #4
    Join Date
    Feb 2009
    Posts
    28
    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

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    you can run any query from VBA just by using
    docmd.runsql strSQL
    (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?
    Me.Geek = True

  6. #6
    Join Date
    Feb 2009
    Posts
    28
    Here is the query...
    Last edited by kvkanuri; 03-03-09 at 13:34.

  7. #7
    Join Date
    Feb 2009
    Posts
    28
    sdaafafd code removed
    Last edited by kvkanuri; 03-04-09 at 16:52.

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    So if you're already defining the SQL string in the code, why not:
    docmd.runsql strSQL
    As earlier suggested?
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •