Why does this SQL not work?

[Patrick Fisher]

Hi All
The function below is intended to create a table called tblNewTable
which is a copy of tblProducts in another database, the name of the
database is held in strFileName.

The problem is that the function below refuses to treat strFileName as
a string
Function CopyDB(strFileName)
Dim x As String
x = DLookup("[DataPath]", "CurrentUser")
Dim strSQL As String
Dim Db As Database
Set Db = CurrentDb
strSQL = "SELECT tblProducts.* INTO tblNewTable IN "
strSQL = strSQL & strFileName
strSQL = strSQL & "FROM tblProducts;"
Db.Execute strSQL, dbFailOnError
End Function

Thanks in advance
Patrick

[Peter Russell]

Looks OK on the surface.

What exactly are you passing via strFileName?
Does it have any spaces in it? If so put square brackets round the whole
path.

Peter Russell
Patrick Fisher previously wrote:

Hi All
The function below is intended to create a table called tblNewTable
which is a copy of tblProducts in another database, the name of the
database is held in strFileName.

The problem is that the function below refuses to treat strFileName as
a string
Function CopyDB(strFileName)
Dim x As String
x = DLookup("[DataPath]", "CurrentUser")
Dim strSQL As String
Dim Db As Database
Set Db = CurrentDb
strSQL = "SELECT tblProducts.* INTO tblNewTable IN "
strSQL = strSQL & strFileName
strSQL = strSQL & "FROM tblProducts;"
Db.Execute strSQL, dbFailOnError
End Function

[MacDermott]

Try adding a space in front of FROM.
The way you have it, your destination string will run together with FROM.

HTH

"Peter Russell" <ru***@127.0.0.1> wrote in message
news:me***********************@russellscott.btinte rnet.com...

Looks OK on the surface.

What exactly are you passing via strFileName?
Does it have any spaces in it? If so put square brackets round the whole
path.

Peter Russell
Patrick Fisher previously wrote:

Hi All
The function below is intended to create a table called tblNewTable
which is a copy of tblProducts in another database, the name of the
database is held in strFileName.

The problem is that the function below refuses to treat strFileName as
a string
Function CopyDB(strFileName)
Dim x As String
x = DLookup("[DataPath]", "CurrentUser")
Dim strSQL As String
Dim Db As Database
Set Db = CurrentDb
strSQL = "SELECT tblProducts.* INTO tblNewTable IN "
strSQL = strSQL & strFileName
strSQL = strSQL & "FROM tblProducts;"
Db.Execute strSQL, dbFailOnError
End Function