Opening 2 Access Databases at once

[KingElvis]

I have an ACCESS database that is getting extremely large (>50mb) and is becoming very unwieldy to upload. Furthermore, the file is likely to grow considerably in the future.

I thought that it might be wise to begin a second database (let's call it 'DBFile2').

My first question is how does one open TWO databases?

My existing code is as follows:

<%
If validation = TRUE Then
strDBPath = Server.MapPath("..\..\db\DBFile1.mdb")
Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
Server.ScriptTimeout = 150
%>

My second question is how does one specify which database to use in an SQL statement. How would one write the following:


<%
'the following should be from DBFile1
strSQL = "SELECT * FROM table 1 WHERE "&strSQL & " ORDER BY surname;"

'the following should be from DBFile2
strSQL = "SELECT * FROM table 1 WHERE "&strSQL & " ORDER BY surname;"
%>

 

[CassidyHunt]

When you open up the recordset you would have to select which connection to use:

rs.open SQL statement, Connection , Cursor Type, lock type, Options

If you use a connection.excute statement then you would just execute the connection of the database you want to query. If you need more code examples let me know.

Cassidy