*Resolved* How to retrieve access table and column information

[Flustor]

Hi

I need to upload Access tables to Oracle and I was wondering if anyone had a better way of doing it than the way I'm thinking...

I'm going to write a VB app to read the column headings from Access and create the script for Oracle. My two problems are:

a) Determining the datatype of a column in Access
b) Reading table names from Access

Anyone know of a way to solve my access problems or another way to do this?

Thanks very much

Flus

[Flustor]

Found out how to obtain the data I needed through the database object in VB:

Get table name
DatabaseObject.TableDefs(6).Name

Get table type (0 = user defined table)
DatabaseObject.TableDefs(6).Attributes

Get field type
10 = Text
DatabaseObject.TableDefs(6).Fields(0).Type

Get field size
DatabaseObject.TableDefs(6).Fields(0).Size

I've also found the Access table which stores object information (the equivalent of sysobjects in MSSql Server). It's called MSysObjects

Outlined below are the codes and their meaning:

Column Name	 Column Type	 Column Size
YesNo	                1	                 1
Byte	                2	                 1
Num Integer	3	                 2
Num Long	                4	                 4
Auto Number	4	                 4
Currency	                5	                 8
Num Single	6	                 4
Num Double	7	                 8
Date Time	                8	                 8
Text(50)	               10	                 50
Text(255)	               10	                 255
Memo	               12	                 0

[James Stanich]

Snippet - Determine Database Field Type

Sub FieldExample()
 
   Dim rs As ADODB.Recordset
 
   Dim fld As ADODB.Field
 
   Set rs = New ADODB.Recordset
 
   ' Open the recordset, specifying an SQL statement
   ' and a connection string.
 
   rs.Open "Select * from authors", "DSN=pubs;UID=sa"
   
   Debug.Print "Fields in Authors Table:" & vbCr
 
   ' Loop through each Field object in the
 
   ' Fields collection of the table and display properties.
 
   For Each fld In rs.Fields
 
      Debug.Print "Name:  " & fld.Name & vbCr & _
 
            "Type:  " & fld.Type & vbCr & _
            "Value: " & fld.Value
 
   Next fld
 
   ' Close the recordset.
 
   rs.Close
 
End Sub