Append Query!!!! Help

Ok this is where weirness exceeds itself.

I manually ran the query through excel and pasted into the import table, ran the append table query which worked (finally one that works) however if you run the delete query it takes the data out of both tables???? when the query only specifies the import table. I have attached a zip file with one days data in it. But I still dont get why the make table wont make a table and the first append query wont append.

Your assistance is most welcome.
 
Last edited:
OK, this is what I did. Bear in mind I don't have any of the linked tables and I had to convert the db to Access 2000, because that's what I have.

I took a copy of tbl_Import Cenceo and pasted the structure only as a new table. I took your query qry_appendtable and amended it to point at the new table I just made. I ran the query and it appended all the data in exactly the way you would expect. This demonstrates that there's nothing odd about the data and there's nothing odd about the query. I suggest you do the same sort of thing as me. Copy tbl_Cenceodata and paste it as an internal table in your database. Change your append query to point at the internal table and run it. I suspect it will work fine.

That only leaves the table you are pasting into. When you are trying to view the data in tbl_Cenceodata, are you looking at it as a linked table in your database, or are you opening the linked mdb where the file really resides? I supect that if you open the linked mdb, you may see the data. Is there anything in the permissions on the drive where this resides that may be screwing things up? Like you have write and modify permissions but not read?
 
Ok I was being a bit thick with my delete query and worked out why everything was being deleted.... muppet!!

Followed your instructions regarding copying and pasting the structure only, not sure how to make it and internal table only, i presume that you mean that its not to be a linked table. I dont have any of the cenceo tables linked, but still no success. The append query wont successfully run unless I am in the design view of the actual query and I wonder whether this is why it wont append the appropriate table????

I have now built another append query which has the four linked tables directly appending the import table. Fingers crossed.


Any other ideas
 
tbl_Cenceodata is a linked table in the db you sent me. That means it's being held in another Access file. Have you split the database into a front and back end?

The append query runs just fine from a double click for me.

Tell me about the linked tables held on G:\MID and the permissions on G:\MID
 
G:mid is where the actual cenceo database lives, I dont have a front and back end, Should I?????.

The Cenceodata table I had stupidly linked to my import table, hence the fauxpa with the delete query..

The other tables in the cenceo database is our employees and our calendar but I haven't managed to get as far as using them yet.

As far as I am aware there are no permissions required but I have to watch the db size our it put our network down, done that a couple of times.

My append table query which moves the data from the import table to the main table works fine, it seems to be the extraction of the data in the first place that seems to be the problem. As i mentioned before the append query wont run and I get an ODBC timeout unless i sit in the design view of the query, then it brings up the data in datasheet view but doesnt append it to the import table. At the moment I am running the extract for each day through excel(which the odbc connection is far happier) and manually pasting it into the database, I just thought it would have saved a lot of time running it straight into a db, but I dont think it wants to play.

Any further suggestions????

Thanks so much for your help
 
I've never had a lot of luck with ODBC connections. Excel is always a lot less fussy about its data sources so what you say seems reasonable.

The question about front ends and back ends was a red herring. I thought your Cenceo datatabase was another Access database. What is it, by the way?

Well I think I'm out of ideas, other than drinking a lot of beer...

Happy Hogmanay!
 
it is just an Access database, but the other database has three pieces, data, import and system, but Im not sure how all of that is done, as Im picking up after someone has left. I think im doing ok for self taught access, never seen a database before july.

Any hoo, will just have to copy and paste the data manually.

Thanks for your help on this you've taught me a lot.

Happy Hogmany

Thanks
 
Last edited:

Users who are viewing this thread

Back
Top Bottom