getting fiiltered data from remote db

[fly_champ]

I have been trying to figure out how to (sucessfully)
transfer filtered data from a remote database (using a different file extension) into my local access in a temp table for calculation.

The information stored in the remote db = time clock in/out times, ID numbers, and Names.

the information I want in the local DB = total hours worked per day per person

I tried to use the "in" operator in a local query, but also to no avail( I think that the db being located on the server nullifies this one )

I have been trying to use the recordset property, but to no avail

here is my (haphazard) code

Private Sub opendb_Click()

Dim db As dao.Database
Dim rs As Recordset
Dim strDbName As String
Dim strPass As String
Dim Filter As Date

'Define as Static so the instance of Access doesn't close when the procedure ends.
Static acc As Access.Application

' set path, password, etc
strDbName = "y:\database\HourTrack.tmt"
strPass = "blahblahblah"
Filter = #7/27/2004# ' will be variable here eventually
   
   'set references
   Set acc = New Access.Application
   acc.visible = True
    Set db = DBEngine.OpenDatabase(strDbName, False, True, ";pwd=" & strPass)
   acc.OpenCurrentDatabase strDbName, False, strPass
   
   ' filter the records using a select statement
   'can you set a recordset equal to a query? (accrual is a query)
    Set rs = _
        db.OpenRecordset("SELECT * " & _
        "FROM Accrual WHERE TimeIn = " & Filter)
        
    With rs
        Do While Not .EOF
            'not sure about the debug.print
            'should be able to copy to local db here, but syntax escapes me
            Debug.Print , .Fields(0), .Fields(1), .Fields(2)
            .MoveNext
        Loop
    End With


   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing

End Sub

I may be going about this in all the wrong ways, but I haven't found a better one.
thank you for your time

 

[mdmilner]

For Starters, OpenDatabase only works for Access databases.
It's a DAO (Data Access Object) which is really a set of Microsoft specific libraries. You're going to have to use ADO and the various connection objects to go after other database types.

What's a *.tmt database file?

Mike

 

[fly_champ]

sorry for not explaining that the *.tmt is just a access 97 database renamed so access will not redily link to it (for security?) it is for an outdated timeclock program that is not going to change so I must work with it.

If I could find a way to link the tables in the remote database, all my problems would dissappear. Maybe there is a way to link tables via code where file extensions don't matter.

 

[bat17]

the extension does not matter.
Doing the links manualy right-click and Link Tables...

In the open file dialog, click on advanced... then click delete to remove the pre-set criteria. this will then let you find all files when you click "Find Now".

Doing it in code would be with something like

Sub linkTable()
Dim strConnect As String
Dim dbs As Database
Dim tdfLinked As TableDef

Set dbs = CurrentDb
Set tdfLinked = dbs.CreateTableDef("tblNewTable") 'name for linked table
tdfLinked.Connect = ";DATABASE=C:\2reports\test.tmt"
tdfLinked.SourceTableName = "tblRemoteTable" ' name of source table
dbs.TableDefs.Append tdfLinked

Set tdfLinked = Nothing
Set dbs = Nothing
End Sub

HTH

Peter