Solved

Export Objects where Objects does not exists in the receiving MDB

Posted on 2013-05-10
9
331 Views
Last Modified: 2013-05-13
I am writing code to export a group of objects to multiple database sources.  How do I export when the object only exists in the current db?

I have tried a TransferDatabase, however I am getting an Error 3011, how do I force the issue?

              If rs1.RecordCount > 0 Then
                
                 'Check for .ldb file -  the APDMS_MDB in use or left open by user
                            
                            strSQL3 = "Select  ObjectName, ObjectType from tblExportList"
                    
                            Set rs3 = curdb.OpenRecordset(strSQL3)
                                rs3.MoveFirst
                                    Do Until rs3.EOF
                                        
                                        nObjName = rs3.Fields("ObjectName")
                                        nObjType = GetObjectTypeConstant(rs3.Fields("ObjectType"))
                                            
                                            'Replaces existing Database objects with revised versions.
                                            DoCmd.TransferDatabase acExport, "Microsoft Access", gAPFilePath, nObjType, nObjName, nObjName
                                       
                                       rs3.MoveNext
                                    Loop

Open in new window


Thanks,

k
0
Comment
Question by:Karen Schaefer
  • 5
  • 3
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39156742
test this  codes


Set rs3 = curdb.OpenRecordset(strSQL3)
     rs3.MoveFirst
         Do Until rs3.EOF
         
               If DCount("*", "msysobjects", "[name]='" & rs3.Fields("ObjectName") & "'") > 0 Then
               
                    nObjName = rs3.Fields("ObjectName")
                    nObjType = GetObjectTypeConstant(rs3.Fields("ObjectType"))
                    
                    'Replaces existing Database objects with revised versions.
                    DoCmd.TransferDatabase acExport, "Microsoft Access", gAPFilePath, nObjType, nObjName, nObjName
               End If

           rs3.MoveNext
         Loop

Open in new window




.
0
 

Author Comment

by:Karen Schaefer
ID: 39156777
How does this affirm that the object exists in the receiving mdb?  How do I force the export of the object?


K
0
 

Author Comment

by:Karen Schaefer
ID: 39156789
your code suggestion if I understand is comparing the currentdb sysobjects to the tbllist.

correct?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39156855
oops, misread your q,

are you transferring all objects ? or just tables?

if you are, then just backup your currentdb.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Karen Schaefer
ID: 39156921
Tell me if I might be on the right track.  - Also just thought of a possible roadblock - I only want to export certain objects, so using msysobjects in the current db will be showing all objects and not just the ones for export.  My process includes importing all the objects form the development version of the mdb into the export tool.  And I do not want to export those objects within the export tool use only.

k
                            strSQL3 = "Select  ObjectName, ObjectType from tblExportList"
                    
                            Set rs3 = curdb.OpenRecordset(strSQL3)
                                rs3.MoveFirst
                                    Do Until rs3.EOF
                                        nObjName = rs3.Fields("ObjectName")
                                        nObjType = GetObjectTypeConstant(rs3.Fields("ObjectType"))
                                       
                                       strObjSQL = "Select Name from msysobjects IN '" & gAPFilePath & "'"
                                       Set rsObj = curdb.Recordsets(strObjSQL)
                                       Do Until rsObj.EOF
                                            nObj1 = rsObj.Fields("Name")
                                       
                                       
                                       
                                       If DCount("*", "msysobjects", "[name]='" & nObjName & "'") > 0 Then
                                            'Replaces existing Database objects with revised versions.
                                            DoCmd.TransferDatabase acExport, "Microsoft Access", gAPFilePath, nObjType, nObjName, nObjName
                                     End If

Open in new window


Still not sure how to use the transferdatabase  to force the export of the object.
0
 
LVL 84
ID: 39161547
TransferDatabase doesn't care if the object exists in the target database or not, so I'm not sure exactly what your issue is. For example, if I have a table named "tblOne" in my development database, I can transfer it to any other database using TransferDatabase, regardless of whether tblOne exists in the target or not.

In other words, your error is occurring for some reason other than what you assume. Can you pinpoint the line where it fails, and confirm the values in your various variables and recordset fields? I'm curious whether the object is named properly in you tblExportList table ...
0
 

Author Comment

by:Karen Schaefer
ID: 39161907
What is the object type id for acReport?

'
Function GetObjectTypeConstant(strObjType As String) As Long

Dim GetObjectType As Integer
      Select Case strObjType
             Case "acForm"
                  GetObjectType = 2
             Case "acModule"
                  GetObjectType = 5
             Case "acQuery"
                  GetObjectType = 1
             Case "acTable"
                  GetObjectType = 0
             Case "acReport"
                  GetObjectType = 0
             Case Else
                   GetObjectType = -1
        End Select
        GetObjectTypeConstant = GetObjectType
End Function
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39161934
Case "acReport"
                  GetObjectType = 3


--------------------------------
acDefault      -1      
acDiagram       8      Database Diagram (Microsoft Access project)
acForm              2      Form
acFunction      10      Function
acMacro              4      Macro
acModule      5      Module
acQuery              1      Query
acReport              3      Report
acServerView      7      Server View
acStoredProcedure      9      Stored Procedure (Microsoft Access project)
acTable              0      Table
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39162339
Turns out that my code for determiner the GetObjectType  for Reports was missing from my code.

Thanks.
K
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
HasData 9 37
I need to be able to get MAX(date)-1 from table. 4 26
append to an ms access field 6 25
Find missing numbers in Access Table PrimaryKey 9 12
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now