Retrieve data from non-linked table

[Montez659]

Man, I know I have seen this topic discussed before (just about every time we discuss splitting the db) but I just can't figure out what to search for. Basically, I have a simple password textbox that I want to look up in the back end but not permanately link the tables so that the table would be visible in the front end.

If I could be pointed to some posts, I will make sure to bookmark them this time!

 

[xenou]

Perhaps you want an "IN" clause?

SELECT MyField FROM MyTable IN 'C:\MyDatabase.mdb'

#&958;

 

[Montez659]

I think that is exactly what I need. I shall take a look at that in a bit. Gotta fix some other bugs first.

 

[Montez659]

I get an error in my FROM clause but I think it has to do with storing the data. How do I retrieve the data into a recordset? This is what I have, and I am pretty sure that the CurrentDB is causing the problem, since it isn't technically the current DB, but I am not sure how to modify it.

strSQL = "SELECT * FROM [tblUsers] IN '\\Lit-tzg-file-01\Public\Personnel\Audit DB2_be'"


Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do While Not rs.EOF
        passString = rs![pWord1]
    rs.MoveNext
    Loop
End If

MsgBox (passString)

 

[Montez659]

I am completely lost in this. I've been reading so much, but my head is swirling with DAO, ADO, ODBC, OleDB and so much more that I literally have no idea what I am looking for.

I have Access 2007, and I have never tried to access a table that wasn't already linked until now. Just about every example shows some snippet but has CurrentDB and I don't need to use the current db, I need a different one (I think, who knows!).

Direction is needed and appreciated!

 

[xenou]

I don't normally work with external databases in code but I would think you only need to open the external database, and then use it the same as the current database. By this I mean, create a Database object (DAO.Database) and then use it instead of the CurrentDB object. Of course, close it when you are finished.

Try this example, but use the full path instead of example.accdb:
http://www.functionx.com/vbaccess/databases/dao/open.htm

----------------------------------------
If this should work or not I'm not sure, but it seems odd that the path doesn't have an extension on it (.accdb or .mdb):
SELECT * FROM [tblUsers] IN '\\Lit-tzg-file-01\Public\Personnel\Audit DB2_be'

 

[Montez659]

Yeah, I noticed that all the examples had the extension on it, and I was going to try that.

I appreciate the help, I will play around with it and let you know if I get it to work.

 

[Montez659]

Alright, I think I got it. Instead of using CurrentDB though I changed it to db. The example you gave was quite helpful!

Here is what the final looked like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim conString As String
Dim strSql As String
        
conString = "\\Lit-tzg-file-01\Public\Audit_Database_be.accdb"

Set db = DBEngine.Workspaces(0).OpenDatabase(conString)

strSql = "SELECT * FROM [tblUsers]"
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do While Not rs.EOF
        passString = rs![pWord1]
    rs.MoveNext
    Loop
End If
db.Close
MsgBox (passString)

Appreciate the help and direction!