Appending Queries from one Access DB to another using ADOX 
Author Message
 Appending Queries from one Access DB to another using ADOX

I have to databases One with queries in and the other with none in. Some of
the queries are standard select queries and some others are cross-tab
queries. When i use the 'Procedures' object to append the queries vb appends
on query and then i get an error which is "DBID is Invaild" Does any one
have a idea on where i am going wrong. I have included my code below.

Thanks Tim.

Option Explicit
Private Const m_File_Location As String = "Some Location"

Private Sub cmdUpdate_Click()

    Dim M_OBJ_FSO           As Scripting.FileSystemObject
    Dim M_FSO_FOLDER        As Scripting.Folder
    Dim M_FSO_FILE          As Scripting.File

    Dim M_ADO_CATALOG       As ADOX.Catalog
    Dim M_ADO_TEMP_CAT      As ADOX.Catalog

    Dim M_ADO_QUERY         As ADOX.Procedure
    Dim M_ADO_TEMP_QUERY    As ADOX.Procedure

    Dim M_ADO_QUERYS        As ADOX.Procedures
    Dim M_ADO_TEMP_QUERYS   As ADOX.Procedures

    Dim M_ADO_COMMAND       As ADODB.Command
    Dim M_ADO_TEMP_COMMAND  As ADODB.Command

    Dim M_ADO_TABLE         As ADOX.Table

    Set M_OBJ_FSO = New Scripting.FileSystemObject
    Set M_FSO_FOLDER = M_OBJ_FSO.GetFolder(m_File_Location)

    Set M_ADO_CATALOG = New ADOX.Catalog
    Set M_ADO_TEMP_CAT = New ADOX.Catalog

    Set M_ADO_COMMAND = New ADODB.Command

        M_ADO_TEMP_CAT.ActiveConnection = Get_Connection(m_File_Location &
"\Update.mdb")

        For Each M_FSO_FILE In M_FSO_FOLDER.Files

            If Right(LCase(M_FSO_FILE.ShortName), 3) = "mdb" Then

                If Not InStr(1, M_FSO_FILE.Name, "00") > 0 Then

                M_ADO_CATALOG.ActiveConnection =
Get_Connection(M_FSO_FILE.Path)

                    For Each M_ADO_QUERY In M_ADO_TEMP_CAT.Procedures

                        Set M_ADO_TEMP_COMMAND = M_ADO_QUERY.Command

                        M_ADO_COMMAND.CommandText =
M_ADO_TEMP_COMMAND.CommandText

                        M_ADO_CATALOG.Procedures.Delete (M_ADO_QUERY.Name)
                        M_ADO_CATALOG.Procedures.Append M_ADO_QUERY.Name,
M_ADO_COMMAND

                        Set M_ADO_TEMP_COMMAND = Nothing

                    Next

                End If

            End If

        Next

    Set M_FSO_FOLDER = Nothing
    Set M_OBJ_FSO = Nothing

    Set M_ADO_TEMP_CAT = Nothing
    Set M_ADO_CATALOG = Nothing

End Sub

Private Function Get_Connection(ByRef Database_Path As String) As
ADODB.Connection

    Set Get_Connection = New ADODB.Connection

        Get_Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& Database_Path & ";Persist Security Info=False"

    Set Get_Connection = Get_Connection

End Function



Tue, 26 Oct 2004 22:04:25 GMT  
 Appending Queries from one Access DB to another using ADOX
Hi Tim,

We need to add a new command object to the target database each time. Here
is correct code:

For Each cp In cx.Procedures

    Set cm = New Command
   cm.CommandText = cp.Command.CommandText

    cx2.Procedures.Append cp.Name, cm
    Set cm = Nothing
Next

Luke

(This posting is provided "AS IS", with no warranties, and confers no
rights.)



Fri, 29 Oct 2004 20:45:25 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. ADOX Appending a view to an Access 97 db

2. ADOX Views.Append hides Query in Access

3. export data from one access db to same db on another machine using vb6

4. Cannot view queries created using ADOX in Access 2000 interface

5. ADOX Views.Append Hides View in Access

6. How Do I Run Append Query's In VB3 (DB Newbie)

7. Querying an access 2000 db using VB

8. Parameterized queries in an Access db are slow using oledb drivers

9. Visual Basic App using Foxpro DB vs Access DB vs SQL Server DB

10. Visual Basic App using Foxpro DB vs Access DB vs SQL Server DB

11. accessing records in one secure DB from another secure DB - programatically

12. Exporting Tables From One Access DB to a New One

 

 
Powered by phpBB® Forum Software