Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

How can I get a MSAccess macro to stop automatically when I get a primary key violation?

Hi,

I've written a macro that loads data from a text file into an MSAccess database. The macro is triggered by a command line so the whole process is automatic and requires no user intervention. I've noticed that if I try to load data that creates a primary key violation, an error msg appears on the screen and waits until there is some user intervention to close down the msg box and rollback any previous inserts. This process needs to be automatic. I've had a look at macro conditions and tried to find one that will look for duplicate values of my primary key field and if any occur to rollback and stop the macro. I can't find any suitable conditions my guess at 'IsDuplicate' is not recognised. Does anyone know of a condition or a way to get my macro to stop automatically in such a situation.

I also need to report this error, which I would like to do by moving the file to an /error dir or renaming the file to incorporate the word 'error'. Does anyone know of a command in the macro to move files?

Thanks,

Tina.
0
TinaKelly
Asked:
TinaKelly
  • 5
  • 5
  • 2
1 Solution
 
shanesuebsahakarnCommented:
I don't know about macros since I don't use them, but would you be averse to using VBA instead ?
0
 
tokerblueCommented:
Following what shanesuebsahakarn said, you should probably use VBA instead of Macros. The biggest problem with Macros is that you can't trap errors. With VBA, you can recognize the errors and adjust the script to handle the error as you want and you can also set your own warning displays.
0
 
tokerblueCommented:
Click on Tools -> Macro -> Convert Macros to Visual Basic

From there, go into the Modules tab and look at the converted code. If you paste up the code, we can help you trap the error.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TinaKellyAuthor Commented:
Hi,

Thanks for replying. Sorry about the delay, comments didn't appear on website until this morning.

Here is my VB code:-
Option Compare Database
'----------------------------------------------------------
' UPS
'
'----------------------------------------------------------
Function UPS()
On Error GoTo UPS_Err

    DoCmd.TransferText acImportDelim, "UPS Delivery Import Specification", "UPS", "C:\CSBC\UPS\in\UPS.txt", False, "", 850
    DoCmd.Quit acSave

UPS_Exit:
    Exit Function

UPS_Err:
    MsgBox Error$
    Resume UPS_Exit

End Function

Assume from this then that I need to add some code within the UPS_Err function to exit if duplicate primary key exists? Also guess this is where I will also move my file to an error dir as this will only occur during an error.

Can I then convert this VB code back into a macro, or will I need to run the VB from the command line when I initiate the load into the database.

Thanks very much,

Tina.
0
 
shanesuebsahakarnCommented:
OK, initially, I wouldn't import the data directly into your table. Import it into a temporary table instead, which has the same structure as your real table. It's then fairly simple to check for primary key violations with something along the lines of:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [TempTable].ID FROM [TempTable] INNER JOIN [RealTable] ON [TempTable].[ID] = [RealTable].[ID]")
If Not rst.EOF
   'Found some primary key duplicates!
Else
   'Didn't find any duplicates, so we can run an append query to copy records from temp to real, and clear out temp
End If
rst.Close
Set rst = Nothing

Where TempTable is the temporary table, RealTable should be changed to the UPS table, and ID is the name of the primary key field.
0
 
TinaKellyAuthor Commented:
Hi,

Thanks for your suggestions. I have been working on this and have had some success, but still have a query about the INNER JOIN compare code. When I debug the module it fails at this point with a 'Type mismatch' error. The fields that are being compared are both Text types and have the same characteristics (the temp table is a copy of the master one). I can't get the module beyond this point.

    DoCmd.TransferText acImportDelim, "UPS Delivery Import Specification", "UPS_temp", "C:\CSBC\UPS\in\UPS.txt", False, "", 850
         
    Dim rst As Recordset
   
    Set rst = CurrentDb.OpenRecordset("SELECT [UPS_temp].[Delivery Number] FROM [UPS_temp] INNER JOIN [UPS] ON [UPS_temp].[Delivery Number]= [UPS].[Delivery Number]")

    If Not rst.EOF Then
        Rem Move Invalid file to \error dir
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.MoveFile "C:\CSBC\UPS\in\UPS.txt", "C:\CSBC\UPS\error\UPS.txt"
        Rem Delete contents in UPS_temp table
 
        Rem Quit macro
        DoCmd.Quit acQuitSaveNone
    Else
         Rem Load data into UPS table
        DoCmd.TransferText acImportDelim, "UPS Delivery Import Specification", "UPS", "C:\CSBC\UPS\in\UPS.txt", False, "", 850
        Rem Delete contents in UPS_temp table
    End If
   
    rst.Close
    Set rst = Nothing
    DoCmd.Quit acSave

Also, I am able to get the file to move to an \error dir when violation occurs and I'm playing around with deleting data in temp table (have tried creating then dropping table, rolling back transaction and deleting data - but for all of these scenarios the compiler complains about my Dim declarations when they are either within or outside my function giving compile error - user defined type not defined, even when I follow the exact syntax of the help manual. Any suggestions.

Thanks,

Tina.
0
 
shanesuebsahakarnCommented:
If you are using A2K or later, you may need a reference to DAO - open a code module, click Tools->References, and then make sure Microsoft DAO 3.6 is ticked. You then need to change the Dim line to:
Dim rst As DAO.Recordset

You might also consider using the Name or FileCopy commands rather than using the FileSystemObject - either of these commands doesn't require a reference to the VBScript library.

To delete the contents of the temp table, don't delete it - just use something like:
CurrentDb.Execute "DELETE * FROM UPS_temp"

You should also close the recordset before your first Quit statement - just put:
rst.Close
Set rst = Nothing
before it, otherwise you may experience some database corruption.

HTH!
0
 
TinaKellyAuthor Commented:
Hi,

Thanks for all the advice. It's all working perfectly now, including the file move when violation occurs and the delete of the temp table.

One final thing that I would like to ask is how to run this module from my command line. I was running my macro using the following:-
cd C:\Program Files\Microsoft Office\Office
MSAccess "C:\CSBC\UPS\bin\UPS.mdb" /cmd "UPS" /x "UPS"
calling the UPS macro.
Is there a way that I can call the module directly from the command line?

I've been playing around with getting the macro to call the module - I've used the OpenModule Action, but this just opens the module it doesn't run it. Is there some other action to run the function in the module automatically?

Thanks very much,

Tina.
0
 
shanesuebsahakarnCommented:
You can use the RunCode action in your macro - set the function name to UPS() and that should do it. Modules are just the containers for functions - you call the function rather than the module :)
0
 
TinaKellyAuthor Commented:
It worked once I changed the function name so that it wasn't the same as the Module name!

Thanks very much for everyone's help!

Tina.
0
 
TinaKellyAuthor Commented:
Thanks for all your great advice.

Tina.
0
 
shanesuebsahakarnCommented:
Glad I could help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now