Pulling Data from external table in another MS Access database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerry Mc Cauley - 04 Jun 2006 23:00 GMT

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

Signature

Jerry

Reply to this Message

Douglas J. Steele - 04 Jun 2006 23:33 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello ....
>
[quoted text clipped - 13 lines]
>
> Thanks

Reply to this Message

Jerry Mc Cauley - 04 Jun 2006 23:45 GMT

I hate to ask ... how?

Signature

Jerry

> The IN clause needs to be after the FROM clause, before the WHERE clause:
>
[quoted text clipped - 19 lines]
> >
> > Thanks

Reply to this Message

Douglas J Steele - 05 Jun 2006 12:26 GMT

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?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I hate to ask ... how?
>
[quoted text clipped - 21 lines]
> > >
> > > Thanks

Reply to this Message

Jerry Mc Cauley - 07 Jun 2006 03:11 GMT

Hi ....

The sql statement will not complie ...

I get a "Expected: List separator or )"

Any ideas???

Signature

Jerry

> Dim dbCurr As DAO.Database
> Dim rsCurr As DAO.Recordset
[quoted text clipped - 52 lines]
> > > >
> > > > Thanks

Reply to this Message

John Spencer - 07 Jun 2006 12:43 GMT

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')

> Hi ....
>
[quoted text clipped - 64 lines]
>> > > >
>> > > > Thanks

Reply to this Message

Douglas J Steele - 07 Jun 2006 12:48 GMT

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'")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Check the parentheses to see that they are matched - equal number of (
> and ). There seem to be too many (
[quoted text clipped - 36 lines]
> >>
> >> 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
[quoted text clipped - 33 lines]
> >> > > >
> >> > > > Thanks

Reply to this Message

Jerry Mc Cauley - 05 Jun 2006 15:58 GMT

Doug,

Thanks for the help ---- I will try this tonight.

Signature

Jerry

> I hate to ask ... how?
>
[quoted text clipped - 21 lines]
> > >
> > > Thanks

Reply to this Message

Jerry Mc Cauley - 04 Jun 2006 23:49 GMT

How?

Signature

Jerry

> The IN clause needs to be after the FROM clause, before the WHERE clause:
>
[quoted text clipped - 19 lines]
> >
> > Thanks

Reply to this Message