Table corrupted when appended to (1 Viewer)

Seems to be a weird situation. TableA is the main data table used in several operations in my database. When I append new data from tableB to tableA, tableA is corrupted. The fields are the same in both tables. By that I mean the normal operations that are executed from tableA no longer work! I have discovered that after the append to tableA, if I export tableA as an Excel file and than import the Excel file, naming it tableA, that with the appended data, everything works!?!
Anyone know what is going on?

What is your query for append records?

What do you mean by corrupt, weird letters/signs or what?

Here is my VBA for the insert:

' ***** Append 'jobsAdd' to 'jobs' ******
strSQL = "INSERT INTO jobs ( Type, [Start Date], Route, TrackingNo, [Job type], Address, [Deliver to / Collect from], [Assign to], [Group], [Time], [Received by], SplGrossHrRate, SplDrvHrRate, MC, [Note] ) SELECT jobsAdd.Type, jobsAdd.[Start Date], jobsAdd.Route, jobsAdd.TrackingNo, jobsAdd.[Job type], jobsAdd.Address, jobsAdd.[Deliver to / Collect from], jobsAdd.[Assign to], jobsAdd.Group, jobsAdd.Time, jobsAdd.[Received by], jobsAdd.SplGrossHrRate, jobsAdd.SplDrvHrRate, jobsAdd.MC, jobsAdd.Note FROM jobsAdd;"
DoCmd.RunSQL strSQL

When I examine the 'jobs' table after the insert I see nothing amiss. But, when I attempt to run a command I get garbage. Then I export the 'jobs' table as an Excel file and then import the Excel file naming it 'jobs' and everything works as it should.

corrupt meaning:
unable to open the table?
the data is in weird letters?
access wont open anymore?

Ranman256 said:

corrupt meaning:
unable to open the table?
the data is in weird letters?
access wont open anymore?

Click to expand...

None of the above! Access runs fine in all other operations and commands. I can open the table and it looks fine (pretty large table), but, as I have mentioned, won't produce accurate results unless I do the export/import.

Thanks!

Mistre42 said:

Here is my VBA for the insert:

' ***** Append 'jobsAdd' to 'jobs' ******
strSQL = "INSERT INTO jobs ( Type, [Start Date], Route, TrackingNo, [Job type], Address, [Deliver to / Collect from], [Assign to], [Group], [Time], [Received by], SplGrossHrRate, SplDrvHrRate, MC, [Note] ) SELECT jobsAdd.Type, jobsAdd.[Start Date], jobsAdd.Route, jobsAdd.TrackingNo, jobsAdd.[Job type], jobsAdd.Address, jobsAdd.[Deliver to / Collect from], jobsAdd.[Assign to], jobsAdd.Group, jobsAdd.Time, jobsAdd.[Received by], jobsAdd.SplGrossHrRate, jobsAdd.SplDrvHrRate, jobsAdd.MC, jobsAdd.Note FROM jobsAdd;"
DoCmd.RunSQL strSQL

When I examine the 'jobs' table after the insert I see nothing amiss. But, when I attempt to run a command I get garbage. Then I export the 'jobs' table as an Excel file and then import the Excel file naming it 'jobs' and everything works as it should.

Click to expand...

TIME is a reserved word. You will want to check the names of your fields and make sure you are not using other reserved words. In general, most on here will avoid using non-alpha characters in their field names also.

May not be directly related to your issue, but is a problem waiting to happen.

And so is Type especially as it isn't in brackets.

Would [Note] also happen to be a long text or memo field , and it is it that is getting corrupt?

As is Group

You should also delimit your values.

OK, I still have some uncertainty about "won't produce accurate results" because - as you said - it is a large table. How do you KNOW the results are not accurate? Against what are you comparing to produce the sense of inaccuracy?

While we are at it, [Deliver to / Collect from] is a field name with special characters in it. This is NOT a good idea. That is an example of a non-alpha character as Mark_ mentioned in his reply. And put this in the queue of things to consider: Every field that has a space in the name adds three characters to your typing every time you use it. The characters are "[", " ", and "]" of course. But if you remove the space and resolve the issues with reserved words and non-alpha characters in names, you might have less work to do when typing. Not to mention fewer places to make mistakes in typing. But that isn't a high priority change. Just think on it.

At the time that you execute the DoCmd.RunSQL, by any chance are warnings disabled? And if so, can you contrive a situation where you enable the warnings before attempting the append operation?

The answers you are getting from the others are comments about your naming choices but the real issue is to find out why the query doesn't work. And I don't see enough info to make that determination.

Using your JobsAdd table as a staging place for the master Jobs table is NOT necessarily a bad idea. I've used staging tables myself many times. What puzzles me is how appending from JobsAdd to Jobs (with identical fields in both) could cause a problem unless there is an autonumber somewhere in that mix. And yet if you export and re-import, it gets back to normal operation. Most of the cases I can think of would not allow that to happen because the append wouldn't work completely, so the export and import would not pick up the records that had not been correctly appended earlier. And that means we have incomplete information.