SQL Question - I am nearly there!

[Patrick Fisher]

The following code works:

Function CopyDB(TableName)

Dim str, str2, intlen, str3, str4, strFileName
str = Trim(DLookup("[DataPath]", "CurrentUser"))
For intlen = Len(str) To 1 Step -1
str2 = str2 & Mid$(str, intlen, 1)
Next intlen
str3 = InStr(str2, "\")
str4 = Left(str2, str3 - 1)
strFileName = ""
str2 = ""
For intlen = Len(str4) To 1 Step -1
strFileName = strFileName & Mid$(str4, intlen, 1)
Next intlen
Dim x As String
x = DLookup("[DataPath]", "CurrentUser")
Dim strSQL As String
Dim Db As Database
Set Db = CurrentDb
strSQL = "SELECT tblProducts.* INTO TableName IN "
strSQL = strSQL & strFileName
strSQL = strSQL & " FROM tblProducts;"
Db.Execute strSQL, dbFailOnError
End Function

It parses str and copies tblProducts to TableName and puts TableName
into the Database represented by strFileName.

I creates a new table called TableName

How can I make TableName a variable so that it can take the actual
name of the new table being passed to the function?
Thanks
Patrick

[Allen Browne]

Try:
strSQL = "SELECT tblProducts.* INTO [" & TableName & "] IN " & _
strFileName & " FROM tblProducts;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Patrick Fisher" <in**@psoftuk.com> wrote in message
news:3r********************************@4ax.com...

The following code works:

Function CopyDB(TableName)

Dim str, str2, intlen, str3, str4, strFileName
str = Trim(DLookup("[DataPath]", "CurrentUser"))
For intlen = Len(str) To 1 Step -1
str2 = str2 & Mid$(str, intlen, 1)
Next intlen
str3 = InStr(str2, "\")
str4 = Left(str2, str3 - 1)
strFileName = ""
str2 = ""
For intlen = Len(str4) To 1 Step -1
strFileName = strFileName & Mid$(str4, intlen, 1)
Next intlen
Dim x As String
x = DLookup("[DataPath]", "CurrentUser")
Dim strSQL As String
Dim Db As Database
Set Db = CurrentDb
strSQL = "SELECT tblProducts.* INTO TableName IN "
strSQL = strSQL & strFileName
strSQL = strSQL & " FROM tblProducts;"
Db.Execute strSQL, dbFailOnError
End Function

It parses str and copies tblProducts to TableName and puts TableName
into the Database represented by strFileName.

I creates a new table called TableName

How can I make TableName a variable so that it can take the actual
name of the new table being passed to the function?
Thanks
Patrick

[Patrick Fisher]

Thanks Allen
That worked fine
Patrick

On Wed, 24 Nov 2004 16:56:07 GMT, Patrick Fisher <in**@psoftuk.com>
wrote:

The following code works:

Function CopyDB(TableName)

Dim str, str2, intlen, str3, str4, strFileName
str = Trim(DLookup("[DataPath]", "CurrentUser"))
For intlen = Len(str) To 1 Step -1
str2 = str2 & Mid$(str, intlen, 1)
Next intlen
str3 = InStr(str2, "\")
str4 = Left(str2, str3 - 1)
strFileName = ""
str2 = ""
For intlen = Len(str4) To 1 Step -1
strFileName = strFileName & Mid$(str4, intlen, 1)
Next intlen
Dim x As String
x = DLookup("[DataPath]", "CurrentUser")
Dim strSQL As String
Dim Db As Database
Set Db = CurrentDb
strSQL = "SELECT tblProducts.* INTO TableName IN "
strSQL = strSQL & strFileName
strSQL = strSQL & " FROM tblProducts;"
Db.Execute strSQL, dbFailOnError
End Function

It parses str and copies tblProducts to TableName and puts TableName
into the Database represented by strFileName.

I creates a new table called TableName

How can I make TableName a variable so that it can take the actual
name of the new table being passed to the function?
Thanks
Patrick