Link to home
Create AccountLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

code to export a single table to multiple database & include the import specs

Looking for code to export a single Access 2007 table and its Import specification to other databases.  I Need to loop thru a list of active databases (there is a table with this list), then replace the existing table with the new table and its import spec - due to the fact that a few field types have changed, hence the need for the new revised import spec.  There is a process that the user can use to import a csv or txt file hence the import spec on a regular basis.  There are as many as 50 different database involved, this is why I am looking for code to handle this update instead of manually changing each database.

Since I modified this import spec I need to now incorporate it into the other database also.

Any suggestions.

K
Avatar of jerryb30
jerryb30
Flag of United States of America image

How many specs are there for the destination databases?
If just the one(s) you want to transfer, you might try
select * into msysimexespecs in 'path\dbname.accdb' from msysimexspecs
select * into msysimexcolumns in 'path\dbname.accdb' from msysimexcolumns

after transferring the table(s)
SOLUTION
Avatar of jerryb30
jerryb30
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
typos:
strSQL2 = "select * into msysimexspecs in '" &rs!dbname & "' from msysimexspecs"
strSQL3 = "select * into msysimexcolumns in '" &rs!dbname & "' from msysimexcolumns"
Avatar of Karen Schaefer

ASKER

will this replace the existing table or create a new table and add a 1 to the end of the name.?

Thanks for the suggestion, how does this export the actual table definition - hence the field types/sizes?  Does this actually replace the table?

K
It replaces the table.
Ok - How do I handle I want to copy the table & import spec from a particular database into the other databases - If I am reading your code correctly it is copying the table and spec onto itself from the loop data source - ie the referenced database.

I want to copy the new table ("TD_CFIG_Dnld") from database "RC021", also the import spec - then loop thru the list of APno  to replace the existing (TD_CFIG_Dnld) in the other databases.

Here is my code so far:
Sub UpdateImportSpec()

Dim rs As dao.Recordset
Dim strSQL As String, strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strDBname As String
Dim nName As String

   On Error GoTo UpdateImportSpec_Error

strSQL1 = "SELECT ApNo, ApDbms_Db" & _
            " FROM TA_APInfo" & _
            " GROUP BY ApNo, ApDbms_Db" & _
            " HAVING (((ApNo) In (SELECT APDBMS_ACTIVE_AP.APNr" & _
            " FROM APDBMS_ACTIVE_AP;)))"
            
Set rs = CurrentDb.OpenRecordset(strSQL1)
nName = "RC021"
rs.MoveFirst

Do While Not rs.EOF
    strDBname = rs.Fields("APDBMS_DB")
    strSQL = "select * into TD_CFIG_Dnld in '" & strDBname & "' from TD_CFIG_Dnld"
    strSQL2 = "select * into msysimexspecs in '" & strDBname & "' from msysimexspecs"
    strSQL3 = "select * into msysimexcolumns in '" & strDBname & "' from msysimexcolumns"

    DoCmd.RunSQL (strSQL)
    DoCmd.RunSQL (strSQL2)
    DoCmd.RunSQL (strSQL3)
    rs.MoveNext
Loop

   On Error GoTo 0
   Exit Sub

UpdateImportSpec_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UpdateImportSpec of Module modImportSpecs"

End Sub

Open in new window

No, it is selecting the records into new tables on the external databases.
Assuming your strDBName includes full path with the db name, it should work fine.

You'll need to turn warnings off to prevent annoying messages when you are overwriting existing tables in external databases.

what is function of nName?
nName was to be the RC021 name to be passed - but no longer needed.

I ran the code - it updated the table but not the import spec update
before (source mdb) after code ran
User generated image
Why didn't the import spec copy to the new database?

K
Run a query: select [name] from msysobjects where [name] like "msys*"
see if msysimexspecs and msysimexcolumns exist.
I may need to recreate this in A2007.
Working this for Access 2007.
Yes, 2007 but saved in 2003 format, hence the mdb.

I checked and those tables exists and they were created with the code but they are not recognized as system objects.  and the Saved Import spec does not display it as a saved import spec.

Any IDEAS?  in the mean time I am manually incorporating them in to the other databases due to time constraints.

K
Yes, I have ideas. I can import the specification. The Saved Imports don't show, but the specs work in code (DoCMD.transfertext ACImportDelim, etc.
I am trying to find a way to open each db, and run the action necessary to transfer the text.
K:
I have beating myself over the head trying to find a solution,. As it turns out, I was using a relative path for the file name, which did not work.
If you are still interested for the next time, I'll put it together.
It will involve the following:
Create table in external database. (As done)
Delete 2 msysIMEX tables
Delete new function
Export function used to import specs into external database
run code to open the external database, and run the new function.
Jerry
(I will be on the road in AM all day, so answer by 5 AM PDST, or be prepared to wait.)
In source database, create this function:
Function gs()
On Error Resume Next
DoCmd.DeleteObject acTable, "msysimexspecs"
DoCmd.DeleteObject acTable, "msysImexcolumns"
DoCmd.TransferDatabase acImport, "Microsoft Access", ".\db2.accdb", acTable, "msysimexspecs", "msysimexspecs", False
DoCmd.TransferDatabase acImport, "Microsoft Access", ".\db2.accdb", acTable, "msysimexcolumns", "msysimexcolumns", False
End Function

Name it ModGetSpecs

Using your posted code, amend as follows:

Sub UpdateImportSpec()

Dim rs As dao.Recordset
Dim strSQL As String, strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strDBname As String
Dim nName As String

   On Error GoTo UpdateImportSpec_Error

strSQL1 = "SELECT ApNo, ApDbms_Db" & _
            " FROM TA_APInfo" & _
            " GROUP BY ApNo, ApDbms_Db" & _
            " HAVING (((ApNo) In (SELECT APDBMS_ACTIVE_AP.APNr" & _
            " FROM APDBMS_ACTIVE_AP;)))"
           
Set rs = CurrentDb.OpenRecordset(strSQL1)
nName = "RC021"
rs.MoveFirst

Do While Not rs.EOF
    strDBname = rs.Fields("APDBMS_DB")
    strSQL = "select * into TD_CFIG_Dnld in '" & strDBname & "' from TD_CFIG_Dnld"
  '  strSQL2 = "select * into msysimexspecs in '" & strDBname & "' from msysimexspecs" DELETE
  '  strSQL3 = "select * into msysimexcolumns in '" & strDBname & "' from msysimexcolumns" DELETE

    DoCmd.RunSQL (strSQL)
'    DoCmd.RunSQL (strSQL2) DELETE
 '   DoCmd.RunSQL (strSQL3) DELETE
'ADD
DoCmd.TransferDatabase acExport, "Microsoft Access", strDBName, acModule, "ModGetSpecs", "ModGetSpecs", False
call RunFunctionGS (strDBName)
    rs.MoveNext
Loop

   On Error GoTo 0
   Exit Sub

UpdateImportSpec_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UpdateImportSpec of Module modImportSpecs"

End Sub

Add another function in source DB:

Public Function RunFunctionGS(strDBNAME As String) As Boolean

Dim appAccess As New Access.Application
appAccess.OpenCurrentDatabase (strDBNAME)
appAccess.Run "gs"
appAccess.CloseCurrentDatabase
msgbox "RunFunctionGS executed for " & strDBName
End Function

This may not be quick. You can comment out the message box once you are confident it is running properly.

As I mentioned before, this will not show up as saved imports, but the spec name(s) will work in a vba transfer text method.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Awarded point for time an effort - chose to manually update the necessary tables and import specs