Solved

Trouble with ODBC selecting records from external mdb

Posted on 2001-08-09
18
451 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:SE081398
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 14

Expert Comment

by:mgrattan
ID: 6369239
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


0
 
LVL 6

Expert Comment

by:devtha
ID: 6369421
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
0
 
LVL 6

Expert Comment

by:devtha
ID: 6369428
Set dbs = wsp.OpenDatabase(DSNName, False, False, "pathe of the other mdb")
Use the above line for MS Access databases.
0
 
LVL 3

Author Comment

by:SE081398
ID: 6369604
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")

0
 
LVL 6

Expert Comment

by:devtha
ID: 6369619
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
0
 
LVL 2

Expert Comment

by:oui_li
ID: 6369672
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
0
 
LVL 3

Author Comment

by:SE081398
ID: 6369719
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.


0
 
LVL 6

Expert Comment

by:devtha
ID: 6369760
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.
0
 
LVL 3

Author Comment

by:SE081398
ID: 6370080
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:SE081398
ID: 6370085
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.
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 6370317
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?
0
 
LVL 3

Author Comment

by:SE081398
ID: 6370366
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.

0
 
LVL 14

Accepted Solution

by:
mgrattan earned 200 total points
ID: 6370517
How about a Sub routine?

Public Sub DoAutoLink()
    Dim dbLink As DAO.Database, db As DAO.Database
    Dim tdfLink As DAO.TableDef, tdf As DAO.TableDef
    Dim sTable As String, sConnect As String
   
    On Error GoTo AutoLink_EH
   
    Set dbLink = OpenDatabase("d:\program files\office2000\office\samples\northwind.mdb")
    Set db = CurrentDb
   
    For Each tdf In dbLink.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
            sTable = tdf.Name & "_Historical"
            Set tdfLink = db.CreateTableDef(sTable)
            sConnect = ";DATABASE=" & dbLink.Name
            tdfLink.Connect = sConnect
            tdfLink.SourceTableName = tdf.Name
            db.TableDefs.Append tdfLink
        End If
    Next tdf
   
Autolink_Exit:
    db.Close
    dbLink.Close
    Set db = Nothing
    Set dbLink = Nothing
    Set tdf = Nothing
    Set tdfLink = Nothing
    Exit Sub
AutoLink_EH:
    MsgBox "Error - " & Err.Number & ":  " & Err.Description
    Resume Autolink_Exit
End Sub
0
 
LVL 6

Expert Comment

by:devtha
ID: 6372442
CAUTION: linking and relinking over the network is time consuming...
0
 
LVL 3

Author Comment

by:SE081398
ID: 6373825
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
0
 
LVL 6

Expert Comment

by:devtha
ID: 6373843
:-)
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 6373870
Excellent!
0
 
LVL 3

Author Comment

by:SE081398
ID: 6373932
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.

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now