Improve Code . .

[Evagrius]

Hello all,

I am using the below code to replace data in a table. The data is contained in a stored Excel File. The code works, however, after it runs it adds an additional table called "Name Autocorrect Save failure." - and a new record is added to that table each time I run the code.

In addition, the autonumber "ID" field continues to increment, even after the code deletes all the records before replaing them - can I reset the autonumber in the code? I would be grateful for any help!

Dim MyInt As Integer
MyInt = MsgBox("Are you sure you would like to " & _
"clear the existing records and replace " & _
"them with the most recent data?", vbYesNo + vbQuestion, "Title")
If MyInt = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete FROM TableName")
Me.Requery
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TableName", "C:\text.xlsx", True, "b4:l5000"
DoCmd.SetWarnings True
Me.Requery
MsgBox "The source data has been updated.", vbExclamation, "Title"
End If

 

[boblarson]

Autonumbers should not matter what they are. If you care, then you are using them for the wrong reasons.

The only way to reset an autonumber is to clear the table completely out and then compact and repair the database.

 

[Evagrius]

Thanks Bob - do you have any advice on the "Name Autocorrect Save Failure"? Thank you!

 

[boblarson]

Thanks Bob - do you have any advice on the "Name Autocorrect Save Failure"? Thank you!

Yeah, make sure you turn Name AutoCorrupt (I mean AutoCorrect) off. It shouldn't be on - it is one of the worst things to cause corruption there are.

 

[Evagrius]

Sorry to impose Bob - one more question - do you think the way I am going about clearing the table and pasting data is a good way to go? Is there a better way to do the same thing?

 

[boblarson]

Sorry to impose Bob - one more question - do you think the way I am going about clearing the table and pasting data is a good way to go? Is there a better way to do the same thing?

Yes, I think it is a good way to go.

 

[Evagrius]

Thank you Bob!!!!!

 

[PaulWilson]

On the subject of AutoCorrect...
I know it's not perfect and folks like Allen Browne have done a good job of describing the problems, but we all need to rename database objects from time to time. I find that AutoCorrect works with Table and Queries. The fun stops there. It won't reach into forms and event procedures reliably. Change the name of a control and chances are any attached event procedure will stop working.

I inherited a database that works well, but the developer did not use any consistent naming convention. It's a maintenance nightmare. It would be helpful if I could get a complete list of form fields and their properties so I could rename things consistently.

Getting a list of all table and query properties is described in the Access Cookbook. What about a list of FORM fields and their properties? How can I do that?

 

[spikepl]

Check this thread for examples of looping over controls in a form http://www.access-programmers.co.uk/forums/showthread.php?t=211092

And open a new thread when you have a question only vaguely related to some other thread. This makes it easier to find for the enjoyment of future users.