Pass through make table query?

[R2D2]

I need to make a pass through query that is a make table query. As a normal pass through query, "SELECT user FROM dual" works just fine, forcing the user to login, and then selecting their User ID. I would like to store this in a table so that I can access it from other queries without forcing this query to be rerun each time (which in turn forces the user to relogin each time). I've tried "SELECT user INTO tblUserID FROM dual" as my Pass Through Make Table Query, but it gives me an ORA-00905 error: missing keyword.

Any ideas?

 

[Pat Hartman]

You can't run a make table query as a pass-through since this query type is unique to Access. You can lookup Create Table in help. That will allow you to create a table in an ODBC data source and then you could use an append query to add data to it. However, this plan will fall apart in a multi-user environment.

Therefore, capture the user's id and password and logon to the ODBC database for them. This technique works no matter how many different ODBC sources your app links to. You just need to connect to each of them. The posted code is an Oracle connect string. If your RDBMS is something else, you may need to adjust the connect string.

Public Function ConnectToOracle()
Dim WgtDB As Database
Dim strConnect As String
Dim F As Form
Set F = Forms!frmLogon
gUserID = Nz(F!txtUSER_ID, "Empty")
gPassword = Nz(F!txtPSWD, "Empty")
gNetWareID = Nz(F!txtNetWareID, "Empty")
strConnect = "ODBC;UID=" & gUserID & ";PWD=security;DSN=weight;Database=WGT"
Set WgtDB = OpenDatabase("", False, False, strConnect)
WgtDB.Close
End Function