What is wrong with this sql (export a linked table)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry if this is a dup post but I got a server error on the first try.

I have a split db and need to export one of the linked tables to a 3rd db as
a table (not linked)

Here is the code I am trying to use:
Dim strSQL As String
On Error GoTo Errorcheck


strSQL = "SELECT * " & _
"INTO tblPrice_Catalog IN " & Application.CurrentProject.Path &
"\PriceList.mdb" & _
" FROM tblFinal_Price_Catalog;"
CurrentDb.Execute strSQL, dbFailOnError

Here is the error info:
?err.Number
3067
?err.Description
Query input must contain at least one table or query.

thanks in advance.
 
Do you have a space in Application.CurrentProject.Path?
Print out strSQL and have a look at it.
Paste strSQL into a QueryDef and see what it looks like in the QBE grid.

BTW, an alternate syntax is [Database].[tablename]

(david)
 
There are spaces in the Application.CurrentProject.Path string but that is
not something I can control because the user might put the application mdb
anywhere on thier machine. when I copy it to the QueryDef it gives me the
same error and will not let me into design view. It does come back with the
: highlighted as the error point in the path C:\Documents and
Settings\..........


david@epsomdotcomdotau said:
Do you have a space in Application.CurrentProject.Path?
Print out strSQL and have a look at it.
Paste strSQL into a QueryDef and see what it looks like in the QBE grid.

BTW, an alternate syntax is [Database].[tablename]

(david)



ES said:
Sorry if this is a dup post but I got a server error on the first try.

I have a split db and need to export one of the linked tables to a 3rd db as
a table (not linked)

Here is the code I am trying to use:
Dim strSQL As String
On Error GoTo Errorcheck


strSQL = "SELECT * " & _
"INTO tblPrice_Catalog IN " & Application.CurrentProject.Path &
"\PriceList.mdb" & _
" FROM tblFinal_Price_Catalog;"
CurrentDb.Execute strSQL, dbFailOnError

Here is the error info:
?err.Number
3067
?err.Description
Query input must contain at least one table or query.

thanks in advance.
Click to expand...
Click to expand...
 
PMFJI,

Enclose the fully qualified path in single quotes.

The SQL would look like this:

strSQL = "SELECT * INTO [tblPrice_Catalog]"
strSQL = strSQL & " IN '" & Application.CurrentProject.Path &
"\PriceList.mdb'"
strSQL = strSQL & " FROM [tblFinal_Price_Catalog];"


Expanded: ....tblPrice_Catalog IN ' " & ........

Expanded: ......\PriceList.mdb ' FROM .........


It doesn't matter how many spaces are in the path if it is delimited with
single quotes.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ES said:
There are spaces in the Application.CurrentProject.Path string but that is
not something I can control because the user might put the application mdb
anywhere on thier machine. when I copy it to the QueryDef it gives me the
same error and will not let me into design view. It does come back with the
: highlighted as the error point in the path C:\Documents and
Settings\..........


david@epsomdotcomdotau said:
Do you have a space in Application.CurrentProject.Path?
Print out strSQL and have a look at it.
Paste strSQL into a QueryDef and see what it looks like in the QBE grid.

BTW, an alternate syntax is [Database].[tablename]

(david)



ES said:
Sorry if this is a dup post but I got a server error on the first try.

I have a split db and need to export one of the linked tables to a 3rd db as
a table (not linked)

Here is the code I am trying to use:
Dim strSQL As String
On Error GoTo Errorcheck


strSQL = "SELECT * " & _
"INTO tblPrice_Catalog IN " & Application.CurrentProject.Path &
"\PriceList.mdb" & _
" FROM tblFinal_Price_Catalog;"
CurrentDb.Execute strSQL, dbFailOnError

Here is the error info:
?err.Number
3067
?err.Description
Query input must contain at least one table or query.

thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
thanks for the help. It is working now but only the first time. after the
table is in the export db the sql errors because the table is there.

Is there an option in the CurrentDb.Execute that will allow it to overwrite
or how do I delete the table if the error occurs?
 
"Select... Into" means create a new table.

"Insert ... Into" means add new rows to a table.

"Update" means modify existing rows.

You can use Update to also Insert: to do that you just need to
update the rows where the target primary key is null (Join the
target and the source table so that you can make that test).

(david)
 
Back
Top