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

Topic

New Reply

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=C:DatabasesProductReleaseControl.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

Reply | Quote

Replies

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;”

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote