Pulling Data from external table in another MS Access database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello ....

I am attempting to pull a value from a table in another access database. I
really do not want to link or import the table because it is the security
levels for this database. I am attempting to match the userid from a
function fgetusername to the table Tbl_Security.Userid.

I have tried:

DoCmd.RunSQL "SELECT TBL_Security.See_S1 FROM TBL_Security WHERE
(((TBL_Security.UserID) = '" & fGetUserName & "' IN
N:\HRData\Personnel_SFO_Security" & ";"

But I do not get anywhere .... any ideas?

Thanks
 
The IN clause needs to be after the FROM clause, before the WHERE clause:

However, you can only use RunSQL with Action queries (INSERT INTO, UPDATE,
DELETE). You cannot use it with a straight select statement. You'd have to
open a recordset, and retrieve the value from the recordset.
 
I hate to ask ... how?
--
Jerry


Douglas J. Steele said:
The IN clause needs to be after the FROM clause, before the WHERE clause:

However, you can only use RunSQL with Action queries (INSERT INTO, UPDATE,
DELETE). You cannot use it with a straight select statement. You'd have to
open a recordset, and retrieve the value from the recordset.
Click to expand...
 
How?
--
Jerry


Douglas J. Steele said:
The IN clause needs to be after the FROM clause, before the WHERE clause:

However, you can only use RunSQL with Action queries (INSERT INTO, UPDATE,
DELETE). You cannot use it with a straight select statement. You'd have to
open a recordset, and retrieve the value from the recordset.
Click to expand...
 
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT TBL_Security.See_S1 " & _
"FROM TBL_Security WHERE (((TBL_Security.UserID) = '" & _
fGetUserName & "' IN 'N:\HRData\Personnel_SFO_Security')

Do While Not.rsCurr.EOF
' referring to rsCurr!See_S1 will get you the value of that field
' for the current row in the recordset

' This will move to the next row in the recordset.
' It will continue to do this until you try to access the
' row after the last row (i.e.: EOF)
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

However, I think the IN clause may be incorrect: that implies that you have
a Jet database named Personnel_SFO_Security (no extension) in a folder named
HRData on your N drive. Is that what you intended?
 
Hi ....

The sql statement will not complie ...

I get a "Expected: List separator or )"

Any ideas???
 
Check the parentheses to see that they are matched - equal number of (
and ). There seem to be too many (

Try this rewrite (I simply removed the unneeded parentheses).

Set rsCurr = dbCurr.OpenRecordset("SELECT TBL_Security.See_S1 " & _
"FROM TBL_Security WHERE TBL_Security.UserID = '" & _
fGetUserName & "' IN 'N:\HRData\Personnel_SFO_Security.mdb')
 
Thanks, John, but we both missed the need for the closing double quote:

Set rsCurr = dbCurr.OpenRecordset("SELECT TBL_Security.See_S1 " & _
"FROM TBL_Security WHERE TBL_Security.UserID = '" & _
fGetUserName & "' IN 'N:\HRData\Personnel_SFO_Security.mdb'")
 
Back
Top