Link to home
Start Free TrialLog in
Avatar of SE081398
SE081398

asked on

Trouble with ODBC selecting records from external mdb

This question has a couple of issues.  The primary problem is as follows.  I want to select records from an external mdb and create a querydef within the currentDB from those records.  I have an existing DB and existing code that needs to be modified to allow the user to select records from either the currentDB or the old historic DB with historic records. Both databases are the same only with recent and old records.  The recent/currentDB only has records for the past 3 months, the old database has the records from 4 months and older.    I want to be able to use the same SQL string regardless of the datasource.

here's the pseudo code.

select case DBsource 'option btns
     case 1
          set DB = currentDB
     case 2
          Location ="C:\windows\samples\NorthwindOLD.mdb"
          set DB = opendatabase(location,true,false)
end select

SQL = select orders.orderid,orders.customerid from orders
set QD = currentdb.createquerydef("query name",SQL)

Now depending on the case, I want to retrieve records from different locations.  I was thinking of trying to use a pass-through query but I've been having trouble setting the connection string, this leads me to the second problem, but first.  I was thinking of writting the SQL string to dynamically allow for different databases.  Eg: select DB.orders.orderid, DB.orders.customerid from orders. this would pick records from what ever database the user selects and create the querydef in the currentDB

passthrough problem:  I have created a file DSN.  now in my passthrough query I select properties and in the connection string I click the elipses.  This opens the odbc DSN window with the newly created DSN.  When I select the DSN I get an error, "you can not use ODBC to import from,export to,or link to external jet or ISAM database table to your database."

What am I doing wrong.

I think a passthrough may do the trick but I cant get the darn thing to work.

Help!!

SE
Avatar of mgrattan
mgrattan

Simplest solution (IMHO):

1.  Link to your old database tables and change the names of the linked tables to include "_History" as the suffix.

2.  Use VBA code to generate your SQL based on selection of "Current" or "Historical".

Example:

Dim sSQL as String

sSQL = "SELECT * FROM " 

Select Case optGroup1
   Case 1     'Current
       sSQL = sSQL & " Table1"
   Case 2     'Historical
       sSQL = sSQL & " Table1_Historical"
End Select


Have the following function take care of your needs. Have 2 global constants one for current and second for history. Use the function to generate the connectstring for you based upon the DSN for resp. dbs. Then when the user wants to connect to the history db let them connect to that db by using the connect string. For current db use the other connect string.
The function generated the following string for an Oracle database.
ODBC;DSN=dv2;;DBQ=tdvru;DBA=W;APA=T;PFC=1;TLO=0;



Public Function GetConnectDSNString(DSNName$)

On Error Resume Next

Dim wsp As Workspace, dbs As Database, strTmp$, intPos%, i%, mstrSource$   ', strTmpConnectString$

         
         Set wsp = DBEngine.Workspaces(0)
         Set dbs = wsp.OpenDatabase(DSNName, False, False, "ODBC;")
         strTmp = dbs.Connect
         dbs.Close
         
         intPos = InStr(strTmp, "WSID")
         If intPos > 0 Then
            i = intPos    ' Save the current Position
            mstrSource = Left$(strTmp, intPos - 1)
            intPos = InStr(i, strTmp, ";")
            If intPos > 0 Then
               mstrSource = mstrSource & Mid$(strTmp, intPos + 1)
            End If
            strTmp = mstrSource  ' Reset the strTmp
         End If
         intPos = InStr(strTmp, "UID")
         If intPos > 0 Then
            mstrSource = Left$(strTmp, intPos - 1)
            i = InStr(intPos, strTmp, "PWD")
            If i > 0 Then
               intPos = InStr(i, strTmp, ";")
               If intPos > 0 Then
                  mstrSource = mstrSource & Mid$(strTmp, intPos)
               End If
            End If
         Else
            mstrSource = ""
         End If
         
         GetConnectDSNString = mstrSource
End Function
Set dbs = wsp.OpenDatabase(DSNName, False, False, "pathe of the other mdb")
Use the above line for MS Access databases.
Avatar of SE081398

ASKER

Devtha: I used your idea and got the error "can't find installable ISAM"
Set Wksp = DBEngine.Workspaces(0)
Set theDatabase = Wksp.OpenDatabase("historic data", True, False, "c:\data files\projects")

There is a solution on MSKB. This is due to improper installation of MS Access.

http://support.microsoft.com/support/kb/articles/Q155/6/66.asp
SE,
There is no point in using a pass though query on a remote .mdb . MDB's are not servers, so a pass though is useless. The error message you got is pretty clear too. You cannot use ODBC to link to an external jet db. Simplify you life, mgrattan has the nice simple solution...

Will
Devtha. I've been on those articles and I've checked for all the dll's and I have them all installed.  I've got SR1 and 2 installed.  

oui_li: I think you may be right with respect to the odbc external mdb issue.  However, why can I not get a DSN to work right.

Michaels idea is a good one however there are implications.  there's more to my problem than just retrieving old records.  there's a part where I have to write these records to this historic database and I have to look into the implications of changing table names.  I'm going to play with Michaels idea and will update with how it's going.

Guys, thanks.


the code is for odbc but I have added in my post the following lines
Set dbs = wsp.OpenDatabase(DSNName, False, False, "pathe of the other mdb")
Use the above line for MS Access databases.
Michael, I've done some playing with your idea and modified the tables and database.  I've renamed the tables in the historic database to have "Historic" at the end of the tables. within the SQL I've concatinated the variable on the end of the table, as follows:

    Select Case DataSource
        Case 1 'current database
            DB = ""
        Case 2 'historic database
            DB = "Historic"
    End Select

"SELECT tblShift" & DB & ".ShiftID, tblShift" & DB & ".Date, tblshift" ..so on....

I have to go all through the code and look for functions that are involved and pass this db variable as to include them in sql in each function.  Once the smoke clears, it should work out to be faster and easier than trying to figure out an odbc solution as before.  I'm going to try and get it all working by tonight and will update everyone with the outcome.

Mike.. you're are a god.  I bow to you, for I am not worthy.  :) hahah

it's funny how sometimes you can't see the forest through the trees.  

As for the other guys. they provided some really good ideas, thanks fellas.
Michael, I've done some playing with your idea and modified the tables and database.  I've renamed the tables in the historic database to have "Historic" at the end of the tables. within the SQL I've concatinated the variable on the end of the table, as follows:

    Select Case DataSource
        Case 1 'current database
            DB = ""
        Case 2 'historic database
            DB = "Historic"
    End Select

"SELECT tblShift" & DB & ".ShiftID, tblShift" & DB & ".Date, tblshift" ..so on....

I have to go all through the code and look for functions that are involved and pass this db variable as to include them in sql in each function.  Once the smoke clears, it should work out to be faster and easier than trying to figure out an odbc solution as before.  I'm going to try and get it all working by tonight and will update everyone with the outcome.

Mike.. you're are a god.  I bow to you, for I am not worthy.  :) hahah

it's funny how sometimes you can't see the forest through the trees.  

As for the other guys. they provided some really good ideas, thanks fellas.
Sounds like you're on a roll man.  Let us know what it looks like after the smoke clears!

BTW, I think there might have been some misunderstanding on the links.  I meant that you could link to the existing tables and change the names of the links, not the actual table names in the history MDB.  You could even automate this by opening the other MDB (Set db = OpenDatabase("olddatabase.mdb") and then looping through the TableDefs to create your links and then set the linked table names to the TableDef names and concatenating "_Historical" to the link names.  Does that make sense?
Ya,know what you mean and again ... never thought of that.  curious though, I've never changed the links like you mentioned, could you explain your methodology breifly.

ASKER CERTIFIED SOLUTION
Avatar of mgrattan
mgrattan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
CAUTION: linking and relinking over the network is time consuming...
AWSOME!!  Out $%^&*@# standing.  I would never thought of doing this.  I was coming at it from a different angle.  this method will work great because the user will only want to view historic data maybe once a month and for the few times they do they can be patient with the network for the extra 30 Sec while the table relink to the historic database.  I love these simple solutions, they are most often the best.  I've tried this out and it works great.
here's the final solution and how it's implimented.  there are many subs and functions that reference the sql and create recordsets and by redirecting the links I don't have to alter the sql in all those functions. :)

Print btn_click()
'The idea of this routine is to redirect the link of the tables to a different databases.
'the databases are identical, and I did not want to modify or change the SQL
'used in other functions to accomodate different table/field names. All that
'is happening is directing the sql to a different data source. this way everything can stay as it is.
'To reset a link to a table the table must be deleted and then appended back to the collection with a new link location

Select Case DataSource
   Case 1 'current database
       'do nothing leave the tables and links to the  currentDB as is
   Case 2 'historic database
       're-link the tables to the historic database
    Set dbLink = openDatabase "c:\datafiles\projects\
        northwind.mdb")
    Set db = CurrentDb
    For Each tdf In dbLink.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
             If Left(tdf.Name, 2) <> "tt" Then 'do not delete temp tables
            sTable = tdf.Name
            db.TableDefs.Delete tdf.Name
            Set tdfLink = db.CreateTableDef(sTable)
            sConnect = ";DATABASE=" & dbLink.Name
            tdfLink.Connect = sConnect
            tdfLink.SourceTableName = sTable
            db.TableDefs.Append tdfLink
        End If
    End If
Next tdf
 
End Select

'set date variables with a calendar.ocx
period = "=#" & Format(Me!Date, "mm/dd/yyyy") & "#"

SQL = select orders.orderid,orders.customerid orders.date from orders where date " & period & ";"

set QD = currentdb.createquerydef("query name",sql)

DoCmd.OpenReport Report, acPreview
:-)
Excellent!
I love the idea that two heads are better than one.  Michael, this saved me a ton of time and hasle.  thanks.

And Devtha, thanks for the tip on the network traffic with respect to the linking. Considering the user is doing this maybe 10-12 times a year the added traffic at those times is acceptable.

oui_li : I'm curious, I've worked on a VB app that used a local mdb DSN and I can't recall how it was done. Any idea how I get my app to use an mdb DSN.  Or is a DSN only used when accessing data in other formats such as ADO.