-
Good morning friends,
I've been asked by a friend here at work to come up with a program that he can select a table name from a MS Access database and be able to export to Oracle 8 after modifications. I would like to use a combo box to list table names and I'm just wondering how do I get the combo box to show the table names. Most likely going to write this in VBA. Any ideas as to how to show the table names and how the proceed with this application would be greatly appreciated
------------------
Beginner VB Programmer
-
How do you do your documentation in Dbases?
Typically I have a table that has every object in the database and its purpose, created by, etc.
If using a standard naming convention (tblName or tName or something) you can filter off of the prefix characters in that table.
If you're not documenting in this manner, then I would suggest just building a table that contatins the objects you want in a combo box. . .
-
For DAO (3.5) with VB5, the code below will list all of your tables in the access database. However, it also includes the system database, all of which start with Msys. Just add each which does not start with msys to your combo instead of the debug.print:
Option Explicit
Const strDB As String = "c:\temp\plantcalcs.mdb"
Private Sub Command1_Click()
Dim dbDatabase As Database
Dim wrkjet As workspace
Dim tblDef1 As TableDef
Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbDatabase = wrkjet.OpenDatabase(strDB)
For Each tblDef1 In dbDatabase.TableDefs
Debug.Print tblDef1.Name
Next
Set dbDatabase = Nothing
Set wrkjet = Nothing
End Sub
-
The following query will give you all the tables in an MSAccess database (ignore any system tables in the list by adding " and flags=0" to the following query):
select name from msysobjects where type=1
-
Thank you all for your help.
I pretty much have it all working now, but I'm getting a ODBC call failed error when I run this command. Here is my code
VBA in Office 97 (MS Access)
Private Sub Command2_Click()
'Variables
Dim TableSpace As String
Dim TableName As String
'Get whatever is selected in the combo boxes
Forms!Front!cblTableSpace.SetFocus
TableSpace = cblTableSpace.Text
Forms!Front!cblTableName.SetFocus
TableName = cblTableName.Text
'Doit
If TableSpace = "APPS" Then
TableSpace = "EAIAPPS"
DoCmd.TransferDatabase (acExport) ("ODBCDatabase"), ("ODBC;DSN=Oracle8;UID=" ) & TableSpace & ";" & "PWD=APPS;LAUGUAGE=us_english;DATABASE=" & TableName & """"), (acTable), (TableName), (TableName), True, True
endif
exit sub
I think something has to do with how I'm including the 2 variables in my command.
The reason I have those variables is to let the user select the table and then select the tablespace he wants to export it to.
I've spent the last 3-4 hours trying to run this thing a number of different ways ( with out the paraintases) spelling??? and adding different number of quotes.
Please help
Thanks,
JazzBass
------------------
Beginner VB Programmer
[This message has been edited by JazzBass (edited 08-17-1999).]
