Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    16

    Unanswered: opening Access 2000 with another Access 2000 database

    Is there any way to open and read data from one db to another.

    I tried this:

    Dim TheRecordSet As DAO.Recordset
    Dim TheDatabase As DAO.Database
    Dim UserName As String
    Dim Flag As String
    Dim dbName As String

    Set TheDatabase = DBEngine.Workspaces(0).OpenDatabase("first_a2k.mdb ")

    Set TheRecordSet = TheDatabase.OpenRecordset("dbAccess", dbOpenTable)
    TheRecordSet.MoveFirst
    UserName = TheRecordSet!dbPassword
    MsgBox UserName & " is modifying users, this window can not be opened now", vbOKOnly + vbInformation

    (I know it's nonsensical.)
    The problem is that on the line Set TheDatabase = DBEngine... the code quits, that is, it steps right to the end of my Sub.

    -Thanks, Tim

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: opening Access 2000 with another Access 2000 database

    Originally posted by Tim Y.
    Is there any way to open and read data from one db to another.

    I tried this:

    Dim TheRecordSet As DAO.Recordset
    Dim TheDatabase As DAO.Database
    Dim UserName As String
    Dim Flag As String
    Dim dbName As String

    Set TheDatabase = DBEngine.Workspaces(0).OpenDatabase("first_a2k.mdb ")

    Set TheRecordSet = TheDatabase.OpenRecordset("dbAccess", dbOpenTable)
    TheRecordSet.MoveFirst
    UserName = TheRecordSet!dbPassword
    MsgBox UserName & " is modifying users, this window can not be opened now", vbOKOnly + vbInformation

    (I know it's nonsensical.)
    The problem is that on the line Set TheDatabase = DBEngine... the code quits, that is, it steps right to the end of my Sub.

    -Thanks, Tim

    just a guess but I do this to link to SQL....

    Dim Return_Set As DAO.QueryDef
    Set Cur_DB = CurrentDb()

    ' delete qrydef if it exists
    For Each qdf In Cur_DB.QueryDefs
    If qdf.Name = "Local_Data" Then
    Cur_DB.QueryDefs.Delete "Local_Data"
    Exit For
    End If
    Next qdf
    Set Return_Set = Cur_DB.CreateQueryDef("Local_Data", "SELECT *" & _
    " FROM DM_GLOBAL_ACCOUNTS_AGGR_TBL" & _
    " where week between " & From_week & to_week & _
    String_Var & _
    Reef_Dry & BTN & Equ & Service_Contract & _
    Account & RCountry & POR & Load & Discharge & Pod & DCountry & Nvocc & Direction)
    Return_Set.Connect = "ODBC;Description=DataWarehouse;DRIVER=SQL Server;SERVER=scnc044db;UID=globalsales;PWD=global ;Network=DBMSSOCN;"

    The like return_set.connect.....is how I pass the info to access letting it know were to go, im betting that there is an access odbc driver that you can use similarly.
    HTH
    Jim

  3. #3
    Join Date
    Nov 2002
    Posts
    16
    OK, nevermind my original quesiton, I recreated my second db using the same MDW and it seems to work. This code doesn't seem to mind trying to look at my other db:

    Set TheDatabase = DBEngine.OpenDatabase("first_a2k.mdb")

    Set TheRecordSet = TheDatabase.OpenRecordset("tblVersion", dbOpenTable)
    TheRecordSet.MoveFirst
    UserName = TheRecordSet!Version

    But now, how do I read something out of my first db? When I try to step past the last line above (UserName = TheRecordSet!Version) the code jumps straight to the end of the Sub.

    Anyone know how to make this reference?

  4. #4
    Join Date
    Nov 2002
    Posts
    16
    OK, for some reason my code works now. If you looked at my post or posted a response (Jim), thanks!

Posting Permissions

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