Append query not appending

[flhr0175]

HI,

When I view my append query results it is giving me all the information it is going to append. When I Run the query, it says it's about to append that many rows. I hit ok and my table doesn't append. What the heck?:banghead:

It was working fine last week.

 

[jdraw]

Do you get a message after you click OK?
It may say something like

About to update 10 records, and then after clicking OK it
says
3 records appended, 7 records failed to append because of key violations or similar

 

[flhr0175]

Do you get a message after you click OK?
It may say something like

About to update 10 records, and then after clicking OK it
says
3 records appended, 7 records failed to append because of key violations or similar

No message after clicking ok.

 

[jdraw]

Can you post the query sql, or a copy of the database?

Remove anything confidential/private, and post in zip format.

 

[flhr0175]

here is a copy.


Database deleted 5/11/24 at the request of the owner

 

[flhr0175]

the linked table file

I am trying to append from 03Mar16 - 11Mar16
63 records


The attachment was deleted per the request of the data owner 5/10/24

 

[jdraw]

What exactly initiates the update query?
Are you running this from a form? Or the query directly?

 

[flhr0175]

What exactly initiates the update query?
Are you running this from a form? Or the query directly?

I have tried running it from the form and from each of the append queries directly.

 

[jdraw]

What is H:\Activity Bulletin.accdb?

I get an error that that file isn't available or not on my server?????

 

[flhr0175]

What is H:\Activity Bulletin.accdb?

I get an error that that file isn't available or not on my server?????

That's where the db is located on my computer.

 

[jdraw]

I have the database unzipped and loaded at

C:\Users\mellon\AppData\Local\Temp\Activity Bulletin - Copy.accdb

and I relinked the xls file. The relink is fine -- I can read the data.

I don't use macros, so I put another button on form Append Range of Dates
and a click event procedure

Private Sub btn_JUpdate_Click()
   On Error GoTo btn_JUpdate_Click_Error

Debug.Print "Update actioned"
CurrentDb.Execute "Add new Activity Bulletin Range of Dates", dbFailOnError
Debug.Print "Update completed"

   On Error GoTo 0
   Exit Sub

btn_JUpdate_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btn_JUpdate_Click of VBA Document Form_Append Range of Dates"

End Sub

When clicked, I can see "Update actioned"
but I get a 3061 error

So if I run the query with form open and completed or run from the button click directly, I get the errors shown in attached jpgs.

A few comments re your database:
-Date is a reserved word
-names with embedded spaces or special characters will result in syntax issues(sooner or later)
-tables in relational database should have a Primary Key

 

[sneuberg]

I know this doesn't help much but when I renamed the application to Activity Bulletin.accdb and put it on a thumb drive that I made H, relinked to the spreadsheet, ran the query from the Append Range of Dates form with the date range 03Mar16 - 11Mar16, I don't see any problems. The Act Bull 15-16 starts with 82 records and ends up with 145 records. I don't get any messages.

I would like to know how and why this is tied to the H drive. When I run the query when the application is on C drive I get the same error jdraw was getting.

 

[jdraw]

Yes, sneuberg, I have the same concern---What and why is something tied to drive H???

Usually, you can download files, unzip, load up the database (and external files), relink tables to your specific drives/locations and all is well.

I noticed that the excel file had multiple sheets, but don't know/doubt if that is a factor in any of this.

 

[mjdemaris]

The drive designation could be coded in an opening or loading event of the application or form, or perhaps driven from an autoexec macro running a function...