Could not find output table error

Is "H:\Project Numbers\Project Number Master.mdb" the
Current database?

If it is, then you can simply use:

Set dbs = CurrentDb()

rather than the OpenDatabase statement.

If it is not, then your INSERT SQL statement won't work as
JET will look for the "tmptbl1" in the Current database
and you get the error posted. You need to use the In
Clause (check JET SQL Reference in Access Help) to specify
the location on the "tmptbl1".

After fixing the above and if it still doesn't work, try
the statement:

DBEngine.Idle dbRefreshCache

after the statement dbs.Close in your code.

HTH
Van T. Dinh
MVP (Access)



>-----Original Message-----
>I am attempting to run the following code in Access VBA
>module for a form and receive the error message:
>
>"could not find output table 'tmptbl1'"
>
>table1 is a linked table (to MS SQL server)
>creating table tmptbl1 in the same database as the form
>
>Code:
>'create a new temporary table in the local database
>holding the first step of
>'determining what active division has no active
>subdivisions
> Dim dbs As Database
> Set dbs = OpenDatabase("H:\Project Numbers\Project
>Number Master.mdb")
>
>' Create a table with two fields.
> dbs.Execute "CREATE TABLE tmptbl1" _
> & "(DivisionID INT, Active CHAR);"
> dbs.Close
>
>'now insert into the temporary table the following
>information
> stSQL = "INSERT INTO tmptbl1 " & _
> "(DivisionID, Active) " & _
> "SELECT dbo_table1.DivisionID, " & _
> "dbo_table1.Active" & _
> "FROM dbo_table1 " & _
> "WHERE (((dbo_table1.Division_Active) = -
1)) "
>& _
> "GROUP BY dbo_table1.DivisionID, " & _
> "dbo_table1.Active;"
>
>'run the sql statement
> DoCmd.RunSQL stSQL
>
>Any help would be greatly appreciated!
>.
>