Tracking ODBC Connections (Access 2010)

[JeffGeorge]

I have shared 2 databases in 2 different folders. The databases are similar as one is used for testing and one is used for printing a number of forms. Let's call the databases PROD and TEST and lets say that they exist on a server called \\DATABASES. PROD.mdb exists in a folder called \\DATABASES\PROD and TEST.mdb exists in a folder called \\DATABASES\TEST. \\DATABASES is running Microsoft SQL Server for a number of different things on that server. When I look at Enterprise Manager I can see both PROD and TEST listed as databases. Each has the same tables. The end users and myself use 2 different ODBC connections on our workstations to work with the 2 databases.

Here's where things get weird.

The forms that get printed in PROD.mdb pull live data from an Oracle database on a different remote server. In the TEST database it's not supposed to be pulling live data but instead is supposed to require the user to create test data. That's how it used to work when it was designed in Access 2000. However, now when someone goes into TEST.mdb the table they see that is supposed to contain only test data is also pulling live data. And when they make changes to a particular form that authorizes which subsequent reports and forms are available it is also making the change in the PROD.mdb table. The OrderData table in TEST.mdb, which is where they put the manually created test data only has 35 records. Yet, when I have TEST.mdb open at look at OrderData in the list of tables it has 47,129 records, the same number that are in the PROD.mdb version.

How do I track down what's going on in TEST.mdb and keep it from crossing over with PROD.mdb? Where do I find the information that tells me where [TEST.mdb].[OrderData] is looking for its data? Unexpected forms becoming authorized and used in PROD.mdb has caused quite a ruckus to say the least.

[ranman256]

If you want to check the ODBC file paths..

dim db as database
dim tdf as tabledefSet db = CurrentDb
For Each tdf In db.TableDefs
     If tdf.Connect <> ""  Then      debug.print     tdf.connect
Next

MsgBox "Done", , "tbl paths"
endit:
Set tdf = Nothing
Set db = Nothing

[JeffGeorge]

Is that Visual Basic? Where would I put that code and how do I get it to execute?

[ranman256]

press ctl-G (to go into VBE)
From the VBE menu: INSERT , MODULE
then INSERT , PROCEDURE
give it a name like: ShowLinks
change radio option to FUNCTION
click OK

paste the code above between the PUBLIC FUNCTION line and the END FUNCTION line
click save. (save everything)

now make a macro say: mShowPaths
add event RUN CODE

at bottom , in the box FUNCTION NAME type our fuction name above: ShowLinks()
save macro
run macro
press ctl-G (to see the results)

[JeffGeorge]

I was able to create the new module but when I go back to the database and choose to create a macro all I get is a green + sign in front of a drop down that says Add New Action but the drop down doesn't have any options in it.

[ssanfu]

There are a couple of errors in the above code (post #2).
Here is the debugged code:

Public Function showlinks()
    Dim db As DAO.Database

    '  dim tdf as tabledefSet
    Dim tdf As TableDef


    ' db = CurrentDb
    Set db = CurrentDb

    For Each tdf In db.TableDefs
        If tdf.Connect <> "" Then
          Debug.Print tdf.Connect
        End If
    Next

    MsgBox "Done", , "tbl paths"

    Set tdf = Nothing
    Set db = Nothing
End Function

In the IDE, you can click in the procedure and press the F8 key to single step through the code OR press F5 to execute the function.
No macro needed to test.... you're probably not going to run this very much...

[JeffGeorge]

This is the output that it the code created:

ODBC;DSN=BRPP Train;Description=BRPP Train;UID=brppuser;PWD=brppuser;APP=Microsoft Office 2010;
ODBC;DSN=BRPP Train;Description=BRPP Train;UID=brppuser;PWD=brppuser;APP=Microsoft Office 2010;
ODBC;DSN=BRPP Train;Description=BRPP Train;UID=brppuser;PWD=brppuser;APP=Microsoft Office 2010;
ODBC;DSN=BRPP Train;Description=BRPP Train;UID=brppuser;PWD=brppuser;APP=Microsoft Office 2010;

That's the same information I see when I hover over the 4 tables in the database navigation pane. This matches the ODBC data I'm using to connect from my workstation. But how do I see where those tables in the database are actually pulling their data from? Each of those tables, one of which at least is supposedly pulling live data from the Oracle database are shown as icons I'm not familiar with. It's a globe with a thin red line around it with a blue arrow pointing at the line. I can't figure out why this TEST.mdb has tables that are using data from tables in PROD.mdb.

[JeffGeorge]

This is the output that the code created:

ODBC;DSN=BRPP Train;Description=BRPP Train;UID=brppuser;PWD=brppuser;APP=Microsoft Office 2010;
ODBC;DSN=BRPP Train;Description=BRPP Train;UID=brppuser;PWD=brppuser;APP=Microsoft Office 2010;
ODBC;DSN=BRPP Train;Description=BRPP Train;UID=brppuser;PWD=brppuser;APP=Microsoft Office 2010;
ODBC;DSN=BRPP Train;Description=BRPP Train;UID=brppuser;PWD=brppuser;APP=Microsoft Office 2010;

That's the same information I see when I hover over the 4 tables in the database navigation pane. This matches the ODBC data I'm using to connect from my workstation. But how do I see where those tables in the database are actually pulling their data from? Each of those tables, one of which at least is supposedly pulling live data from the Oracle database are shown as icons I'm not familiar with. It's a globe with a thin red line around it with a blue arrow pointing at the line. I can't figure out why this TEST.mdb has tables that are using data from tables in PROD.mdb.

[JeffGeorge]

Whoops. My stupid browser is acting stupid and I can't for the life of me figure out where the delete button is.

[JeffGeorge]

I think I might have figured something out. When I looked at UID brppuser on the SQL server I noticed that it had access to both the PROD and the TEST databases. I created a new user that only has access to the TEST database and now when I open the TEST.mdb I only see the records from tables that are actually in that database. I'm not sure I understand why it was sharing data before as I still can't see where the SQL database is pulling data from the Oracle database.