Importing Access tables -- Solution found - Thanks

[A441OTA]

Hello everyone!

I am new to this forum and new to VB!! I have really learned quite a bit from the postings that I have reviewed so far, now I hope someone can help me with a particular issue (one of MANY!) that I am faced with. It seems as if it should be easy, however, with my naivity I just can't find the "straightforward" answer!

I am programming a user interface in VB 6.0. I will have two databases that I am working with. The first db (shell) contains preprogrammed queries and the second db has the data I need to import into the "shell" database.

It was recommended that I use ADO to connect to the database which is pretty much a foreign language to me right now!

I need to import all of the tables from the second db into the first database through code. The file name and location will be different everytime (both files! ) so the user will be prompted for this information... other than that, the rest of the process should be in code without user intervention.

Any thoughts, ideas, or a nice sawed-off shotgun to put me out of my misery right now!!!

Please tell me this is easy... which is why I can't find any help on the web!!!!!!!

Thanks,
Mary

[peet]

Loop through all tables in the db where you want to get the data from.

use SQL on each of these tables in order to insert data or create new tables and insert data in the other db.

not sure where u want me to start, do you have anything made out already, or do you need it from scratch?

if you need help with the entire operation, you should upload a copy of each of the databases, and let us play arround with it.

[A441OTA]

Thanks for your quick reply Peet!

Let me see if I can break this down better for further clarification...

Database 1 (Shell) -- I created and built some queries just to clean up the data that will be imported in from the second database. This is going to be the main database the users will be connected to for the rest of the program that I create.

Database 2 (Macro data) -- this is a database created by a macro process in SAS. The tables will be set up exactly the same as the main tables in the Shell. There will be one table named "demodata", then there will be a number of tables that fortunately they have agreed to name the same thing everytime, although there will not always be the same amount. This will make loops easier to write hopefully.

I didn't even know where to begin on incorporating the import into my interface. What I have right now is a form that requests from the user the location and name of the Shell database (this will reside on individual user's machines -- not a network). Then another form that requests the same information for the Macro database. That, unfortunately is as far as I have gotten with my limited knowledge.

When I was asked to do this project they made it sound easy -- taking raw data and making it look pretty.... NOT!! It has turned into writing a full-blown VB interface to Access data that will NEVER (the structure of the file will remain the same, but the data is far from "normal") be the same to produce pretty reports!!

I'm not trying to get someone to write this code for me, I just simply have no idea where to begin!!!!

Thanks,
Mary

[NateBrei]

This isn't the most elegant of solutions, but it generally works to copy data from one database/table to another database/table. For this example, I am using 2 ADO Data Controls on a form & a command button. (This could probably be done without a form by using ADO objects & just put the whole thing into a Sub Main in a module.) Also, this example uses two different Access databases which each have only 1 table whose structure is identical.

Private Sub Form_Load()
    Dim strLocationSource As String
    Dim strLocationTarget As String
    Dim myField
    
    'Put your user input source file into a variable (I'm hardcoding the location
    strLocationSource = "C:\DATA\Databases\SiteAccess.mdb"
    With Adodc1
        'Assuming a Microsoft Access provider
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            strLocationSource & ";Persist Security Info=False"
        'Setting CommandType to a table
        .CommandType = adCmdTable
    End With
    
    'Put your user input target file into a variable (I'm hardcoding the location
    strLocationTarget = "C:\DATA\Databases\TestSites.mdb"
    With Adodc2
        'Assuming a Microsoft Access provider
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            strLocationTarget & ";Persist Security Info=False"
        'Setting CommandType to a table
        .CommandType = adCmdTable
    End With
End Sub
 
Private Sub cmdTransferData_Click()
    Dim MyObject
    Dim x As Integer
    Dim y As Integer
    Dim NumberOfTables As Integer
    
    'Sets up error handling in case a defined Source table doesn't exist so _
        it can skip that one
    On Error GoTo TableNotFound
    NumberOfTables = 2  'This would be the maximum number of Tables you would load
 
    'I couldn't find a way to do a For Each loop on a Tables collection, 
    'hence the For loop with the hard-coded number of tables from above
    For y = 1 To NumberOfTables
        Select Case y
            Case 1
                'List first source table name & corresponding target table name
                Adodc1.RecordSource = "Sites"
                Adodc2.RecordSource = "TestSitesTable"
            Case 2
                'List second source table name & corresponding target table name
                Adodc1.RecordSource = "TableDoesntExist"
                Adodc2.RecordSource = "TestSitesBogus"
            Case 3
                'Change the recordsource to the names your next set of tables
            'Repeat this as many times as necessary
        End Select
        'These statements open the recordsource(s) based on above table names
        Adodc1.Refresh
        Adodc2.Refresh
        
        'Walk through each record in source table recordset until EOF
        Do Until Adodc1.Recordset.EOF
            'Issues AddNew method on target table recordset in order to enter new data
            Adodc2.Recordset.AddNew
            'Assuming source & target tables have same table structures
            'Fields are indexed from 0
            For x = 0 To Adodc1.Recordset.Fields.Count - 1
                'Writes value of each source field into corresponding target field
                Adodc2.Recordset.Fields(x) = Adodc1.Recordset.Fields(x)
            Next x
            'Issues Update method on Target recordset to write record
            Adodc2.Recordset.Update
            'Moves to next Source record
            Adodc1.Recordset.MoveNext
        Loop
        Adodc2.Refresh
DoNextTable:
    Next y
    Exit Sub
TableNotFound:
    Select Case Err
        'Traps the error for the non-existing table
        Case -2147217865
            Resume DoNextTable
    End Select
End Sub

I'm sure any number of people in this forum can give you better/cleaner solutions. But, hopefully this can get you started.

Nate

[Killazzz]

Well I've tried the following in an Access environment. I haven't converted it into actual VB. Some syntax will change cause of VBA.

The following will create all tables excluding system tables and will then populate all the tables created.
Sub CREATE_NEW_DB()
Dim rs As Recordset
Dim newrs As Recordset
Dim td As TableDef
Dim newtd As TableDef
Dim fld As Field
Dim db As Database
Dim db2 As Database
Dim f As Integer
Dim i As Integer
'this is your shell db
Set db = CurrentDb
'this is your SAS db
Set db2 = OpenDatabase("c:\test1.mdb")
'this will create all the database tables excluding the system tables as they already exist
For Each td In db2.TableDefs
If Mid(td.Name, 1, 4) <> "msys" Then
Set newtd = CurrentDb.CreateTableDef(td.Name)
For Each fld In td.Fields
With newtd
.Fields.Append .CreateField(fld.Name, fld.Type, fld.Size)
End With
Next fld
db.TableDefs.Append newtd

Set rs = td.OpenRecordset
Set newrs = newtd.OpenRecordset
While Not rs.EOF
With newrs
.AddNew
For f = 1 To rs.Fields.Count - 1
.Fields(f).Value = rs.Fields(f).Value
Next f
.Update
End With
rs.MoveNext
Wend
newrs.Close
rs.Close
End If


Next td




End Sub

[peet]

this is how I would do it.

Private Sub Command1_Click()
    Dim db As Database
    Dim sDBFrom As String
    Dim sDBTo As String
    Dim td As TableDef
    Dim sql As String
    
    sDBFrom = "C:\TEST\From.mdb"
    sDBTo = "C:\TEST\To2.mdb"
    
    
    'check to see if the database exist. if not create it.
    If Dir(sDBTo) = "" Then
        'database does not exist, make the database
        Set db = CreateDatabase(sDBTo, dbLangGeneral, dbVersion40)
        db.Close
    End If
    
   'open the db where you want to get the data from
    Set db = OpenDatabase(sDBFrom)
 
    'copy all the tables using SQL and Execute
    For Each td In db.TableDefs
        'make sure that you do not try to create the system tables,
        'cause they will already be in the db
        If UCase(Left(td.Name, 4)) <> "MSYS" Then
            sql = "SELECT * INTO " & td.Name & " IN '" & sDBTo & "' FROM " & td.Name
            db.Execute sql
        End If
    Next td
    
    'close the db
    db.Close
End Sub

[peet]

this is how I would do it.

Private Sub Command1_Click()
    Dim db As Database
    Dim sDBFrom As String
    Dim sDBTo As String
    Dim td As TableDef
    Dim sql As String
    
    sDBFrom = "C:\TEST\From.mdb"
    sDBTo = "C:\TEST\To2.mdb"
    
    
    'check to see if the database exist. if not create it.
    If Dir(sDBTo) = "" Then
        'database does not exist, make the database
        Set db = CreateDatabase(sDBTo, dbLangGeneral, dbVersion40)
        db.Close
    End If
    
   'open the db where you want to get the data from
    Set db = OpenDatabase(sDBFrom)
 
    'copy all the tables using SQL and Execute
    For Each td In db.TableDefs
        'make sure that you do not try to create the system tables,
        'cause they will already be in the db
        If UCase(Left(td.Name, 4)) <> "MSYS" Then
            sql = "SELECT * INTO " & td.Name & " IN '" & sDBTo & "' FROM " & td.Name
            db.Execute sql
        End If
    Next td
    
    'close the db
    db.Close
End Sub

[A441OTA]

Thank you, Thank you, and Thank you!!!

Thank you Peet, NateBrei, and Killazzz!! You all could never imagine how grateful I am to you right now (I'll get some sleep tonight!!!).

I tried all three approaches and Peet's seems to work the best for what I have (I had to reference the DAO library but that was a quick fix -- is that an issue, to reference DAO and ADO in the same project? It was recommended that I use ADO so I hadn't referenced the DAO).

I owe you all a beer -- look me up when you're in VA!!!!

Thanks,
Mary

P.S. -- this thing gets more and more complicated as I hurdle each step so I'll probably be back, just ignore me when you get sick of my questions!!!! I am trying to find the answers first so I don't wear out my welcome with anyone!!!

Mary >>>>>>>

[NateBrei]

You're welcome. I knew that Peet would be one of the expert resources with the simple, concise method. I'd use his solution, too. In fact, I'd use anything I see that he suggests. They're all good.

Good luck.
Nate

[peet]

thank you Nate

A441OTA, if you are told to use ADO, you should stick to ADO.

I'll make a sample for you later on.

Now... off to the kindergarden with E

later

[peet]

Private Sub Command1_Click()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    
    Dim sDBFrom As String
    Dim sDBTo As String
    Dim sql As String
    Dim cnn As New ADODB.Connection
    
    sDBFrom = "D:\TEST\From.mdb"
    sDBTo = "D:\TEST\To.mdb"
    
    
    'check to see if the database exist. if not create it.
    If Dir(sDBTo) = "" Then
        'database does not exist, make the database
        Set cat = New ADOX.Catalog
        cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBTo
        Set cat = Nothing
    End If
    
    'setup db connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBFrom
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cnn
 
    'copy all the tables using SQL and Execute
    For Each tbl In cat.Tables
        Debug.Print tbl.Name & "  :  " & tbl.Type
        'Make sure this is a table and not a query / view
        If UCase(tbl.Type) = "TABLE" Then
            'make sure that you do not try to create the system tables,
            'cause they will already be in the db
            If UCase(Left(tbl.Name, 4)) <> "MSYS" Then
                sql = "SELECT * INTO " & tbl.Name & " IN '" & sDBTo & "' FROM " & tbl.Name
                cnn.Execute sql
            End If
        End If
    Next tbl
    
    'close the connection
    Set cat = Nothing
    Set cnn = Nothing
    MsgBox "Import finished", vbInformation
End Sub

[A441OTA]

Thank you again Peet!!!! Nate is right, I have read through a lot of your replies -- you're wonderful!

Until next time..... Mary

[hsuwz]

when the DBto has password, how to write the code?

[peet]

Originally posted by hsuwz
when the DBto has password, how to write the code?

what do you mean hsuwz ?