Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting a Dynamic Data Source in Access 2000 (MN Access 2000)

    Hello All!

    I'm working on an Access 2000 database and in the global module a connection to the actual database itself is set up (I've pasted the code below):

    Function GetDBConnection()

    'Open the Connection Object
    Set Conn = New ADODB.Connection
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CatabasesProductReleaseControl.mdb;"

    GetDBConnection = Conn

    End Function

    This function is called several places through out the database in the forms and reports. Below is an example:

    Dim GetArgentinaRS As ADODB.Recordset
    Dim strArgentinaSQL

    'Open the ADO Recordset object
    Set GetArgentinaRS = New ADODB.Recordset
    GetArgentinaRS.ActiveConnection = Conn

    strArgentinaSQL = "SELECT * FROM RRAImportation WHERE Country = 1"
    GetArgentinaRS.Open strArgentinaSQL

    The problem is I need to be able to put this entire database where I want. Since the data source is set in the global file, unless you put the database where the global file is set, or you change the global file to reflect the new location of the database, the database fails as soon as it hits code the calls the GetDBConnection() function. So, here is my question - Can I set the data source to be dynamic so that where ever the database is placed it will run correctly? If I can't then is there another way I can run SQL statements in code like the one above without using the conn object for my connection?

    Thanks for your help,

    Amanda Segelstrom

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting a Dynamic Data Source in Access 2000 (MN Access 2000)

    Is ProductReleaseControl.mdb the backend you're connecting to? Is it always going to be in the same folder as your application? If so, you can use something like this:

    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "ProductReleaseControl.mdb;"
    Charlotte

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting a Dynamic Data Source in Access 2000 (MN Access 2000)

    Charlotte,

    Yeah, when I say "application" I'm talking about the actual database itself and the forms and reports that are included in it. There are some cases in a form where I am calling the function to create and open the Conn object so can use it to grab a recordset from the tables in the database so I can manipulate and use in the in VB behind the form. So the database is accessing itself I guess - does that make sense? Anyway, so on the Conn.Open the DataSource is set to location of the database itself. But then I can't move the database into another directory without changing the data source in code. So, is there anyway for the database to "know" where it is and input that dir/path into the Data Source? I was thinking about the DAO use of CurrentDb, but then I've got to go through all of the code behind the forms and reports and look at how I am using the Conn object and redo it to fit with the DAO format. If this doesn't make sense let me know and I will try to explain it better....


    Thanks again!

    Amanda

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Setting a Dynamic Data Source in Access 2000 (MN Access 2000)

    As Charlotte suggests in her response, CurrentProject.Path tells you the directory the database is located in, so that should work just fine. I presume you are working with an unbound form and ADO in order to do some esoteric things with the data. In case you aren't aware of it, you can work with bound forms, which will quickly eliminate most of the VBA, and is significantly quicker than doing recordset manipulations. Another issue to consider is that the ADO version which shipped with Access 2000 is pretty limited. There are later versions available, and if you haven't installed them, you might want to.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting a Dynamic Data Source in Access 2000 (MN Access 2000)

    In that case, just use this:

    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.FullName

    That will work even if they change the name of the database.

    However, I must tell you that having your tables and your forms, reports, modules, etc., all in one database is a recipe for trouble. Those objects have a tendency to get corrupted when you make a lot of changes, and you can't update the the objects without overwriting the data in the tables. Not a good plan even if you're the only one who uses it. I sometimes do initial design in a single file, but as soon as I have the table design the way I want it, I split the database.
    Charlotte

  6. #6
    Lounger
    Join Date
    Apr 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting a Dynamic Data Source in Access 2000 (MN Access 2000)

    Great idea! I think I'm going to split the actual DB from the forms and reports that use it. Thanks so much, you are AWESOME!

    Regards,

    Amanda

Posting Permissions

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