[RESOLVED] How to alter the structure of a MS Access database by VB6 code at runtime?

[camoore]

I have a .mdb Access database, which contains several tables. Say it is C:\Test.mdb with Tables 100, 101 and 102

How can I achieve the following by VB6 code at runtime :

1. Delete an existing table.
2. Create a new table of, say, 20 fields specifying what sort of fields they are, give it a new table name (eg. Table103) and enter it into the database.
3. Copy an existing Table of the database back into the database but give it a new Table name.

I have spent a lot of time reading forum FAQ and MS Articles on this topic, but all the code I have tried has resulted in various errors.

Could anyone please give me some straightforward code which will work and from which I can go on to develope a solution? Right now I just can not seem to get started. Yet it seems a fairly simple set of problems.

camoore

Wales, UK

[dee-u]

Have a search for ADOX, it should give you clues.

[camoore]

Will indeed search and "look for clues".

But what would be greatly appreciated is some actual code which will work without errors and get me started on this - any of the 3 sub topics will help.

camoore

Wales, UK

[dee-u]

This is a good read, refer to the Using Data Definition Language section.

For your two questions (you can execute them using your connection object):
1.DROP TABLE TableName
2.CREATE TABLE TableName
(CustomerID INTEGER,
[Last Name] TEXT(50),
[First Name] TEXT(50))
3. This could be a good reference.

[camoore]

Thanks, will read and get back to thread.

camoore

Wales, UK

[camoore]

Having read the suggested references and quite a lot of MS articles, I believe that I have just about found how to allow VB6 code at runtime to 1. Generate a new datatable and insert it in a .mdb database and 2. Delete a Table from a .mdb database. The following code does either. To CREATE a table, Line 50 is omitted. To DELETE a table Line 50 is kept active.

There is one problem though. If you try to delete a table which does not exist or create a table which already exists an error box comes up (not this program's error box). The program's error handling routine does not seem to catch this problem.

Therefore care must be taken through surrounding code to ensure that these conditions can not occur.

The third objective of copying a .mdb table, re-naming it and adding it back into the database I have yet to achieve, but can live with for now.

So progress thanks dee-u. I hope that this code may be of use to other members.


camoore

Wales, UK

Private Sub Command1_Click()         'TEST .mdb table creation / deletion

    'This code either creates a table999 in a .mdb database or deletes table999
    
    'To CREATE the table, OMIT Line50: from the code.
    
    'Note : This program requires that the
    'Microsoft ADO EXT 2.1 for DDL and SECURITY reference be called up.
    
    

    On Error GoTo Line400
    
    Dim tbl As New Table
    
    Dim cat As New ADOX.Catalog
    
    ' Open the Catalog. Establish link to desired .mdb database
    
    cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='C:\RAILPROJECT\db1.mdb';"
    
    tbl.Name = "Table999"       'specify a name for the new table in the .mdb


'Line50: GoTo Line200   '******************************
                                'OMIT this goto line to CREATE a table, leave it
                                'as is to DELETE a table

Line100:                        'code to create a new table

    'next specify the desired fields one by one. This insludes specifying whether
    'numeric or text and if text the maximum number of allowed characters. If this
    'number is not stated, the default value is 255 characters.
    
    'Define the Field headings - ID, Field2, Field3 etc. etc.
    
    tbl.Columns.Append "ID", adInteger           'first field "ID" numeric only
    tbl.Columns.Append "Field2", adVarWChar      '2nd field text,255 chars. max
    tbl.Columns.Append "Field3", adVarWChar      '3rd field text,255 chars. max
    tbl.Columns.Append "Field4", adVarWChar, 50  '4th field text,50 chars. max
    
    On Error GoTo Line400
    
    cat.Tables.Append tbl                   'add the new table to the .mdb database
    
    GoTo Line300


Line200:                          'code to delete a table

    On Error GoTo Line400
    
    cat.Tables.Delete tbl.Name    'delete table 999

    
Line300:
    
    'Close down opened routines
    
    Set cat.ActiveConnection = Nothing
    
    Set cat = Nothing
    
    Set tbl = Nothing
    
    GoTo Line500
    
    
Line400:

    Set cat.ActiveConnection = Nothing
    
    Set cat = Nothing
    
    Set tbl = Nothing

    If Err <> 0 Then
    
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
        
    End If
    
Line500:
    
End Sub

[camoore]

I am going to mark this thread "Resolved" and am most grateful to dee-u for having pointed me towards ADOX as the route to a solution,

This directed me to a search and I found MS articles ms676514 and aa164917 to be very helpful. My code above posted is based upon these.

I can now, to the extent that my simple needs require, automate MS Access from VB code, creating a new Table of my defined characteristics or delete an existing Table from a .mdb dabase.

Through this knowledge I can now exchange data between an EXCEL file and an ACCESS database using code at runtime. This thread has been associated with thread 592742 addressing the EXCEL interface with VB code.

One further point about my above posted code : if an ACCESS cell will need to contain more than 255 characters, it should be set up as a MEMO cell (which allows about 65,000 characters I read). To do this, in my code, use adLongVarChar in place of adVarChar for the field in question.

Thanks dee-u( and si the geek for responses to post #592742).

camoore


Wales, UK