Solved

Get tables with only the ODBC connection

Posted on 2000-04-17
15
358 Views
Last Modified: 2008-03-17
I want to be able to use SQL to get all of the tables in my ODBC connection.  How can I do this?
0
Comment
Question by:nathans
15 Comments
 
LVL 5

Expert Comment

by:KMAN
ID: 2723704
Please elaborate on what "Get all of the tables" means.  Link them?  Import them? List them?
What kind of Database is ODBC connection to?
0
 
LVL 10

Accepted Solution

by:
paasky earned 200 total points
ID: 2723706
Hello nathans,

Here's my suggestion:

SELECT Name
FROM MSysObjects
WHERE Type=4
AND Left([Connect],3)="DSN";

Regards,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2723710
that will list the names of all linked ODBC tables... or do you have something else in your mind?
0
 
LVL 4

Expert Comment

by:berg1375
ID: 2723752
Good info. Thanks paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2723767
Here's a little sample code which I just made that shows all the remote ODBC database tables (in debug window):

Public Function ShowODBCTables()
    Dim tdf As TableDef
    Dim db As DAO.Database
   
    Set db = OpenDatabase("test", dbDriverPrompt, True, "ODBC;DATABASE=;UID=scott;PWD=tiger;DSN=sample")
    For Each tdf In db.TableDefs
        Debug.Print tdf.Name
    Next
   
    Set db = Nothing
   
End Function

You can test it with debug window typing
? ShowODBCTables

Remember to change the connect string matching your system (above was tested with Oracle8)

Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2723773
berg1375, you're welcome. :-)
0
 
LVL 5

Author Comment

by:nathans
ID: 2723783
Adjusted points from 100 to 200
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 5

Author Comment

by:nathans
ID: 2723784
Only one problem it says I have no read access???

this is my database on my machine.  the odbc is on my machine.

I want to access all of the user tables that is in a odbc connection to create crud screens on the fly.  

It sounds like your right now how do I get read access??
0
 
LVL 10

Expert Comment

by:paasky
ID: 2723815
nathans, check in your Tools | Options.. that System Objects are checked visible. You should have read access to that system table if you're logged in as an Admin (by default if you've not defined any security settings)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2723827
I modified my second code a little that it will show linked ODBC tables:

Public Function ShowODBCTables()
    Dim tdf As TableDef
    Dim db As DAO.Database
   
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If left(tdf.Connect, 4) = "ODBC" Then
            Debug.Print tdf.Name
        End If
    Next
   
    Set db = Nothing
   
End Function

0
 
LVL 10

Expert Comment

by:paasky
ID: 2723844
Here's little improved version that also creates a worktable, stores table names there and finally shows data for you:

Public Function ShowODBCTables()
    Dim tdf As TableDef
    Dim db As DAO.Database
   
    On Error Resume Next
   
    Set db = CurrentDb
    db.Execute ("DROP TABLE ODBC_TABLES")
    db.Execute ("CREATE TABLE ODBC_TABLES (TABLE_NAME TEXT(30));")
    For Each tdf In db.TableDefs
        If left(tdf.Connect, 4) = "ODBC" Then
            db.Execute ("INSERT INTO ODBC_TABLES VALUES (" & Chr(34) & tdf.Name & Chr(34) & ");")
        End If
    Next
    DoCmd.OpenTable "ODBC_TABLES"
    Set db = Nothing
   
End Function
0
 
LVL 10

Expert Comment

by:paasky
ID: 2723850
Maybe TEXT(30) is not enough with some databases than Oracle, TEXT(64) might be better size...
0
 
LVL 5

Author Comment

by:nathans
ID: 2723874
I guess I left out to much from my question if you help me get this to work I will double my 200 points to 400 points..


I am working from with in ColdFusion

<cfset datasource="test">
<cfset Table="Employee">


<cfquery name="GetData" datasource="#datasource#">
SELECT Name
FROM MSysObjects
WHERE Type=4;
</cfquery>


It says I do not have read rights... How can I get read rights... I want to be able to create crud screens on the web dynamically...

I already can in Oralce and MS Sql there must be someway I can do it in MS Access.

Thanks again.
0
 
LVL 5

Author Comment

by:nathans
ID: 2723951
Open up the database in Access, go to Tools, Options, View and select Hidden
and System objects.  


Keep in mind that most of the data is stored as OLEObjects, which means that you won't get much information out of the table, even with read permissions.

After that you have to go to Tools, Security and then set the permissions and then the odbc has access to it.!!!

This is awesome

Thanks,
Nathan - Mr. ColdFusion

I now can do this with Oracle, MS SQL, and MS Access.

PS. Since I figured out the last part I will not double it.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2723977
nathans, glad I could assist you.

Best regards,
paasky
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Login screen Access Database 8 29
Getting Run-Time Error 13 - Type Mismatch 3 26
Export Query data to excel file 14 33
append to an ms access field 6 21
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 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

18 Experts available now in Live!

Get 1:1 Help Now