?
Solved

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

Posted on 2003-03-10
12
Medium Priority
?
303 Views
Last Modified: 2012-06-21
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
Comment
Question by:TinaKelly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8103353
I don't know about macros since I don't use them, but would you be averse to using VBA instead ?
0
 
LVL 3

Expert Comment

by:tokerblue
ID: 8103887
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
 
LVL 3

Expert Comment

by:tokerblue
ID: 8103896
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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:TinaKelly
ID: 8110030
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8110497
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
 

Author Comment

by:TinaKelly
ID: 8127980
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8128087
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
 

Author Comment

by:TinaKelly
ID: 8137109
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1000 total points
ID: 8137239
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
 

Author Comment

by:TinaKelly
ID: 8151845
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
 

Author Comment

by:TinaKelly
ID: 8151853
Thanks for all your great advice.

Tina.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8151875
Glad I could help!
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

801 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