Question using the OpenDatabase Method

[fchan]

Hi,

I've written some code to correspond to a button_onclick event. The code is suppose to open another Access database and then use the DLookup function to validate a user's login name and password. However, I'm not sure how to reference the table in the other database. What is the correct syntax?

Here's a sample of my code

Dim wksp As Workspace
Dim db As Database
Dim tdf As TableDef
Set wksp = DBEngine.Workspaces(0)
Set db = wksp.OpenDatabase("s:\felix\LOA_DB_be.mdb&quot

Dim varLogin As Variant, varPassword As Variant
varLogin = DLookup("[LoginName]", "tblLogin", "[LoginName]='" & Me.txtLoginName & "'&quot
varPassword = DLookup("[Password]", "tblLogin", "[Password]='" & Me.txtPassword & "'&quot

When I run this event, Access gives me an error message saying that it can't find the input table or query named "tblLogin" in my DLookup statement.

Any suggestions?

Thanks.

 

[scking]

You have opened a database remotely but are not using the remote database when trying to login. It can't find the tblLogin locally because it is in the remote table ("s:\felix\LOA_DB_be.mdb" I assume).

You could probably write a function in the remote database and 'Run' the function as if it were a library. Just remember, if you want to access a database object a remote database then write it for CodeDb rather than CurrentDb (The default). This is the exact technique used when writing Add-Ins for Access. I suggest you would need to put you login procedure in the remote database and open it with a function.

In remote database
Function RemoteCall()

Dim db As Database
Set db = CodeDb

In the local database
Application.Run(("s:\felix\LOA_DB_be.RemoteCall&quot

Steve King

Growth follows a healthy professional curiosity

 

[PaulF]

I received the same Error Msg when I ran an example to test using DLookUp in another MDB.

Two things you might think about doing
1. Link to the table in the MDB and still use DLookUp
2. Use FindFirst Instead of DLookUp

This example uses FindFirst, and "assumes" that LoginName and Password need to be in the same record for a match to occur.

Dim db As DAO.database
Dim rst As DAO.Recordset
Set db = DBEngine.OpenDatabase(""s:\felix\LOA_DB_be.mdb"&quot
Set rst = db.OpenRecordset("tblLogIn", dbOpenSnapshot)
With rst
.FindFirst "[LogInName] = '" & Me.txtLogInName & "' And [Password] = '" & Me.txtPassword & "'"
If .NoMatch Then
MsgBox "No Match"
Else
MsgBox "Matched Login Name and Password"
End If
End With
rst.Close
Set rst = Nothing
Set db = Nothing

PaulF

 

[fchan]

Thanks PaulF that did the trick.