|
Subject:
|
How to set properties of query in Access VBA
|
|
Posted By:
|
LittleCity
|
Post Date:
|
12/17/2003 10:36:55 PM
|
I need to create a make-table query. The table will be created in another database. I can enter destination database when I set properties of the query in design view. But since the destination database may change each time(users can choose), how can i set this property of query in Access VBA.
Your help will be highly appreciated!
|
|
Reply By:
|
cpopham
|
Reply Date:
|
12/23/2003 11:43:17 AM
|
You will have to use a variable and pass the variable using Code.
I wrote this to insert a string into a table on the fly using a users input. You can adapt it for what you need. Or at least get some ideas as to the direction you need to go:
Option Compare Database
Option Explicit
Dim strResponse As String
Dim strSQL As String
strResponse = InputBox("New table name?")
strSql = "INSERT INTO TblAccessDate ([Date],[Time],[TableName])" & _
" VALUES (#" & CStr(Date) & "#, #" & CStr(Time) "#, '" & strSaveAs & "');"
|
|
Reply By:
|
pjm
|
Reply Date:
|
9/27/2006 8:42:56 AM
|
That last response seems to have a few mystery variables thrown in. Try this:
' These 2 variables get their values from perhaps a form or an inputbox function (you decide)
Dim Tablename as String
Dim DatabasePath as String
Dim qs as String
qs = "SELECT <and some other stuff>"
' The key piece of code
qs = qs & " FROM " & Tablename " AS T IN '" & DatabasePath & "'"
qs = qs & " WHERE <and other magic>;"
You don't need to give the table an alias but it just seems like the thing to do.
-Phil-
|
