Nathan Stanford Sr
asked on
Get tables with only the ODBC connection
I want to be able to use SQL to get all of the tables in my ODBC connection. How can I do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
that will list the names of all linked ODBC tables... or do you have something else in your mind?
Good info. Thanks paasky
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
Public Function ShowODBCTables()
Dim tdf As TableDef
Dim db As DAO.Database
Set db = OpenDatabase("test", dbDriverPrompt, True, "ODBC;DATABASE=;UID=scott;
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
berg1375, you're welcome. :-)
ASKER
Adjusted points from 100 to 200
ASKER
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??
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??
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)
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
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
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
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
Maybe TEXT(30) is not enough with some databases than Oracle, TEXT(64) might be better size...
ASKER
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.
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.
ASKER
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.
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.
nathans, glad I could assist you.
Best regards,
paasky
Best regards,
paasky
What kind of Database is ODBC connection to?