473,224 Members | 1,360 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Code To Transfer VBA Modules


I have the below code to transfer code modules between Access database files. I have three problems, only one of which is annoying.

a) How do you prevent the Save As box from appearing when the instance of Access is closed? I've tried the acQuitSaveAll as well as the acCmdCompileAndSaveAllModules route.

b) The On Error line was added when the code was in Access 2003, when I was getting an error message regarding a bad DLL reference when the code hit the Exit Sub at the end. The message description said to check the parameter types or values being passed. This occured consistently but the error would happen, stop the code, I'd step the code on and no error would occur, so I put a Resume Next there to step past it. Under Access 2000 however, this does not occur at all. Any ideas as to why?

c) The code does not consistently perform the import. Stepping through it appears to do what it's supposed to, and if I exit early then it prompts me to save what its done so far. However, when I let it run on a series of seven other databases, some of them don't seem to correctly do the first handful of modules. At first I thought this was to do with the Save As not appearing for those modules so it did not save them, but as a strange quirk, the more times I run this on the same files, the more modules don't get transferred. If it is not to do with the Save As box, I have a suspicion this is to do with the loops and their indexes in the components. Any suggestions?


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. 'Stand-alone function to synchronise all files in the same directory as this one.
  5. Public Sub RunSynchBatch()
  6.     Call DoBatchSynchronise(Access.Application)
  7. End Sub
  9. 'Cycles through databases in a given directory and performs a synch with this file.
  10. 'If sCycleDir is not supplied, you will be prompted.
  11. Public Sub DoBatchSynchronise(oExportFrom As Access.Application, Optional sCycleDir As String = "")
  12.     Dim nLoop As Integer
  14.     If sCycleDir = "" Then
  15.         'Sets the default entry in the prompt to the same directory as this file.
  16.         With oExportFrom.CurrentDb
  17.             sCycleDir = Dir(.Name, vbDirectory)
  18.             sCycleDir = Left(.Name, Len(.Name) - Len(sCycleDir))
  19.         End With
  21.         sCycleDir = InputBox("Please enter the full path of the directory you wish to update:", "Update Directory", sCycleDir)
  22.     End If
  24.     With Application.FileSearch
  25.         .NewSearch
  26.         .FileType = msoFileTypeDatabases
  27.         .LookIn = sCycleDir
  29.         If .Execute > 0 Then
  30.             For nLoop = 1 To .FoundFiles.Count
  31.                 'For each database found, so long as it is not this one, transfers the code.
  32.                 If .FoundFiles(nLoop) <> oExportFrom.CurrentDb.Name Then Call DoSynchroniseCode(oExportFrom, .FoundFiles(nLoop))
  33.             Next nLoop
  34.         End If
  35.     End With
  36. End Sub
  38. 'Performs a one-shot export of the code modules in the database open in the oExportFrom instance
  39. 'of Access to the file specified by sSendTo.
  40. Public Sub DoSynchroniseCode(oExportFrom As Access.Application, sSendTo As String)
  41.     Dim oAccess As Access.Application
  43.     Call oExportFrom.DoCmd.SetWarnings(False)
  45.     'Checks that the file specified is really an Access database.
  46.     If Right(sSendTo, Len(sSendTo) - InStrRev(sSendTo, ".")) = "mdb" Then
  47.         'Opens a new instance of Access and opens the specified database.
  48.         Set oAccess = New Access.Application
  50.         Call oAccess.DoCmd.SetWarnings(False)
  51.         Call oAccess.OpenCurrentDatabase(sSendTo)
  52.         Call DoAccessImport(oExportFrom, oAccess)
  53.         Call oAccess.DoCmd.RunCommand(acCmdCompileAndSaveAllModules)
  54.         Call oAccess.Quit(acQuitSaveAll)
  55.     End If
  57.     Call oExportFrom.DoCmd.SetWarnings(True)
  58. End Sub
  60. 'Exports all the code files in oExportFrom and imports them into oImportTo.
  61. Private Sub DoAccessImport(oExportFrom As Access.Application, oImportTo As Access.Application)
  62.     Dim sStore As String
  63.     Dim nLoop As Integer, nComps As Integer
  65.     'Uses the location of the oExportFrom file to create a temporary directory to store the
  66.     'exported code.
  67.     With oExportFrom.CurrentDb
  68.         sStore = Dir(.Name, vbDirectory)
  69.         sStore = Left(.Name, Len(.Name) - Len(sStore)) & PATH_SYNCH_DIR & "\"
  70.     End With
  72.     If Len(FileSystem.Dir(sStore, vbDirectory)) <= 0 Then Call FileSystem.MkDir(sStore)
  74.     With oExportFrom.VBE
  75.         'For each project that exists in the database, cycles through the components which exist
  76.         'as part of it.
  77.         For nLoop = 1 To .VBProjects.Count
  78.             With .VBProjects(nLoop).VBComponents
  79.                 For nComps = 1 To .Count
  80.                     With .Item(nComps)
  81.                         'Currently only performs this with Standard and Class modules.
  82.                         If .Type = vbext_ct_StdModule Or .Type = vbext_ct_ClassModule Then
  83.                             'Exports the module into a text file so it can be imported.
  84.                             Call .Export(sStore & .Name & ".txt")
  85.                             Call DoImportModule(oImportTo, .Name, sStore)
  86.                         End If
  87.                     End With
  88.                 Next nComps
  89.             End With
  90.         Next nLoop
  91.     End With
  92. End Sub
  94. 'Searches through the target database to check if the module being transferred already exists.
  95. 'If it does, then removes the module before importing the one exported from the main database.
  96. Private Sub DoImportModule(oSearch As Access.Application, sName As String, sStoreDir As String)
  97.     On Error Resume Next
  99.     Dim nLoop As Integer, nComps As Integer
  101.     With oSearch.VBE
  102.         'NOTE: If you have more than one project in the target database, this will import the
  103.         'requested code module into all of them.
  104.         For nLoop = 1 To .VBProjects.Count
  105.             With .VBProjects(nLoop).VBComponents
  106.                 For nComps = 1 To .Count
  107.                     'Checks that it has found a module of the same name, and that it is a
  108.                     'standard or class module.
  109.                     If .Item(nComps).Name = sName And _
  110.                        (.Item(nComps).Type = vbext_ct_StdModule Or _
  111.                         .Item(nComps).Type = vbext_ct_ClassModule) Then
  112.                         'Removes the module so that when the import is done you don't get auto-
  113.                         'numbered versions of the module.
  114.                         Call .Remove(.Item(nComps))
  115.                         Exit For
  116.                     End If
  117.                 Next nComps
  119.                 Call .Import(sStoreDir & sName & ".txt")
  120.             End With
  121.         Next nLoop
  122.     End With
  123. End Sub
Jul 3 '07 #1
0 1581

Sign in to post your reply or Sign up for a free account.

Similar topics

by: joieva | last post by:
I would like to know how we can transfer data through a secured channel to a non-secured site. I am designing an e-commerce site and getting data from secured site database(on a firewall) and...
by: Glauco | last post by:
hi to all, i'm planning to make a way to transfer file with python. I would like to make the transfer using some crypto api, to make the channel secure. i don't want to write a wrapper to ftp or...
by: James Cameron | last post by:
Hi I'm developing a program and the client is worried about future reuse of the code. Say 5, 10, 15 years down the road. This will be a major factor in selecting the development language. Any...
by: Lauren Wilson | last post by:
Hi folks, Can someone point me to some resources on how to control FTP sessions from and Access application with VBA? Many thanks for all help. --LW.
by: Shaun Wilde | last post by:
I am trying to handle a 404 errors and redirect them to the correct page based on criteria I was hoping to catch the error in the global error handler and forward via Server.Transfer to the...
by: Bart Van der Donck | last post by:
Hello, I'm posting the software for one-FAQ-a-day as described on http://tinyurl.com/qcxw7 (comp.lang.javascript, July 18 2006, titled "CLJ newsgroup FAQ) and on http://tinyurl.com/ppt2s...
by: Valli | last post by:
Hi, My project contains 20 htm pages. All these page conatins login button where the click of login button transfers to login.htm page. All these modules were designed by my colleague. My part...
by: Saurabh | last post by:
Dear All, Can anyone tell me, how to write such a program that can transfer files (either binary or text) behind NAT devices( such as for computers behind firewalls and routers and other NAT...
by: archdin | last post by:
Hello experts, I m working in SAP. But this time i m working on program of weighbridge. An ABAP application is developed to get the vehicle and material information along with the gross and...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.