Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opendatabase (97 2)

    Good Afternoon,
    Can you tell me how to open a database from a module in another database. Both databases are located in the same directory. I am using the following lines of code which are in database one. The database that contains the tables are in .MyDatabasePath (database two)
    I have to be able to add and delete records and also count the number of records in tables in database two from database one. I would like to be able to use Dcount for example to count the number of records in "NFSNameandaddress101" All of the processing will be done in database one.

    I have never done this before. Is there any trick I am missing?

    Thanks so very much
    Cathy

    Public Const MyDatabasePath = "C:File_Parse_AMATESTfile_parse.mdb"

    Set dbsSTD = OpenDatabase(MyDatabasePath)

    Set RST = dbsSTD.OpenRecordset("NFSNameandaddress101", dbOpenDynaset)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opendatabase (97 2)

    The easiest way is to use File | Get External Data | Link Tables... to create a link in 'database one' to the table(s) in 'database two'. You can then treat the linked tables as if they were present in 'database one', but the changes will be stored in 'database two'.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opendatabase (97 2)

    Hans
    I do not want the tables to be linked. I have a module that takes in a file, determinds what kind of file it is and then calls a function that processes it accordingly. (this is in database one) If there is a way to call a function in another database that would work because I could pass the processing off at that point.
    Cathy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opendatabase (97 2)

    You can't use DCount on another database, but you can use DAO methods such as the ones in your first post in this thread. For example:

    Public Const MyDatabasePath = "C:File_Parse_AMATESTfile_parse.mdb"

    ...

    Dim dbsSTD As DAO.Database
    Dim RST As DAO.RecordSet
    Dim lngCount As Long
    Dim strSQL As String

    Set dbsSTD = OpenDatabase(MyDatabasePath)
    Set RST = dbsSTD.OpenRecordset("NFSNameandaddress101", dbOpenDynaset)
    ' Move to last record, otherwise record count isn't defined
    RST.MoveLast
    lngCount = RST.RecordCount
    ' Delete some records (fictitious example)
    strSQL = "DELETE * FROM NFSNameandaddress101 WHERE SomeField = 3"
    dbsSTD.Execute strSQL, dbFailOnError

    ' Always clean up objects when you're done with them
    RST.Close
    Set RST = Nothing
    dbsSTD.Close
    Set dbsSTD = Nothing

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opendatabase (97 2)

    Is there a way to count the records using criteria, the reason I was using dcount. For example I want to see if there are any existing records in the table where the established date = a given date.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opendatabase (97 2)

    You could do something like this:

    Set dbsSTD = OpenDatabase(MyDatabasePath)
    strSQL = "SELECT * FROM NFSNameandaddress101 WHERE DateField = #11/07/2006#"
    Set RST = dbsSTD.OpenRecordset(strSQL, dbOpenDynaset)
    ' Move to last record, otherwise record count isn't defined
    RST.MoveLast
    lngCount = RST.RecordCount

    or

    Set dbsSTD = OpenDatabase(MyDatabasePath)
    strSQL = "SELECT Count(*) As RecCount FROM NFSNameandaddress101 WHERE DateField = #11/07/2006#"
    Set RST = dbsSTD.OpenRecordset(strSQL, dbOpenDynaset)
    lngCount = RST!RecCount

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opendatabase (97 2)

    I am going to work on this, thanks. I have one more favor to ask. How would I turn this dcount into a select statement. This is from my actual code and I am afraid the quotes could mess me up.

    RecCt = DCount("[date_processed]", "Tbl:Processed_date_log", "[Date_Processed] = " & Chr(34) & RPTDATE & Chr(34) & " and [file_processed] = " & Chr(34) & dailyfilename & Chr(34))
    thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opendatabase (97 2)

    You'd use something like this:

    strSQL = "SELECT Count(*) AS RecCount FROM [Tbl:Processed_date_log] WHERE [Date_Processed] = " & _
    Chr(34) & RPTDATE & Chr(34) & " and [file_processed] = " & Chr(34) & dailyfilename & Chr(34)
    Set RST = dbsSTD.OpenRecordSet(strSQL, dbOpenDynaSet)
    RecCt = RST!RecCount

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opendatabase (97 2)

    thanks you are the best. I am going to work on this tomorrow, my day is just about over. Have a good evening.
    Cathy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •