By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,029 Members | 1,262 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,029 IT Pros & Developers. It's quick & easy.

SQL Question - I am nearly there!

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.