Append Query!!!! Help

[Fifer]

HI there,

sorry a bit of a newbie to access. I am trying to do an append query, or should I say I have done an append query.

I am trying to build a database. The first query pulls data from a server with four tables linked into this, when you fnally get the ODBC to connect the data comes through. I have a delete query to clear down my Import table and then an append query to put all the dat from my query into this. This is where it goes all wrong and its so basic.

INSERT INTO tbl_ImportCenceo ( [email id], process, type, changedate )
SELECT qry_email1.[email id], qry_email1.process, qry_email1.type, qry_email1.changedate
FROM qry_email1;

It has worked once and now it wont??????. The data runs and is in the append query but it is not transferring it to the import table. Can somebody tell me why Pleaseeeee!!! this is doing my head in!!!!.

Thanks

 

[neileg]

How do you know the data exists in the append query? Are you getting an error message?

 

[Fifer]

The append query pulls the data from the main query. I have them set up on a macro but because it is such an unreliable ODBC connection its nightmare to run the macro. so I run each query individually, the data comes up but when I check the import table there is nothing there?

Thanks

 

[neileg]

What I don't understand is that when you run an append query, you don't see the data. Access tells you it is about to append X records but you don't see the query results, only the data in the destination table. That's why I'm asking how you know the append query has the data.

If you use the INSERT SQL you set out, you do realise that this will re-run qry_email1, don't you.? If you have a flakey connection, it may be that this query is failing.

A select query will show you the data, but it won't append anything to anywhere.

I'm trying to help, honest!

 

[Fifer]

The actual append query allows a datasheet view. The coding is copied from a successful database??? Can you suggest another type of sql which will do what I want??? Believe me any bit of advise is gratefully received, as I have spent ages comparing with other append queries to find out why this one is not working. I have attached a zip file which has a small peice of the data extracted, hope this might help

Thanks

 

[neileg]

Seeing the data doesn't really help.

If you want to appeand data to a table then an append query is the sql you need.

You should get some messages from Access as you run the append query. What are they?

I wonder if the problem is the fact that "type" is a reserved word in Access and that's wrecking the sql. Try changing the field name and see if this helps.

 

[Fifer]

All the message that comes up is that 'You are about to append a query' which I ok. I will try changing the feild name.
Fingers XXXX

 

[Fifer]

Well I can safely say after about 10 attempts to connect the ODBC changing the field name didnt work. AAAAArggh, V. frustrating. Any other ideas. Please. Can I also say, and this is the weird bit. If I have run the append query and the info has come up in the datasheet view, I dont see it in the import table where it should be going, however if I then run the whole shabang through the macro or even the delete query it is saying that its going to delete, so many thousands of rows from the selected table ( Importcenceo) yet I cannot see anything there. Any Ideas on my little gremlin?????

Thanks so much for your help so far.

 

[Access2000_JS1]

I had an identical problem the other day. Assuming you use the date in its 'correct' format I would suggest you try:

INSERT INTO tbl_ImportCenceo ( [email id], process, type, changedate )
SELECT qry_email1.[email id], qry_email1.process, qry_email1.type, format(qry_email1.changedate,"dd/mm/yy")
FROM qry_email1;

 

[Fifer]

thanks for that will try, however, qry_email1 has a format in it to change the date around. The original format is date and time. will this still work, do you think????.


Many thanks again

Tried it, and it pulls the data but still does not insert it into the import table or not that I can see. Any other ideas. Im not sure if Im now barking up the wrong tree and its just that I cannot see the data in the table.

any suggestions on this one is sooooooo welcome.

thanks

 

[neileg]

Viewing the query in datasheet view is not the same as running the query. You need to run the query before it will update the destination table.

Try turning qry_email1 into a make table query and have a look at the data in the resulting table.

 

[Fifer]

Tried it already. Im in the process of building another database to see if this ones has gremlins. I think it must be putting the dat somewhere as it is 590,976kb and growing, but I just cant see it. Will try this in a new db and see if it works.

thanks again

Built a new db, wich is now only 304kb, phew. However same old same old. A brand new append query and it still dont work.


I think i will check into a loony bin for the new year.

Just discovered a new problem in that my 'make a table' query in that it doesnt make a table??????


any suggestion anyone. Cheers

 

[neileg]

There's a very good loony bin just up the road from here. Just let me know...

Have you done a compact and repair on your db? This will reduce space created in running queries.

Any chance of posting your db here? I know the ODBC connection won't work!

 

[Fifer]

hope this through ok

 

[neileg]

Communication breakdown! None of the queries in here are append queries. And none of the tables are local tables that data could be apended to. Result, I've got nothing to look at!

 

[Fifer]

Sorry I deleted the original db that I built before the IT guys saw the size. so the one i sent was the one I was rebuilding.

Have rebuilt my append and delete queries for you. Very strage though that the maketable query doesnt make a table!! bizarre.

Thanks muchly for your help with this.

 

[neileg]

Well there's nothing wrong with the sql.

I still have a problem with theat field name Type. Just to compound this, there's a module that actually uses the reserved word Type! I can't see this module being called anywhere, try deleting it just to see what happens.

Come to think of it, I would start a new database and only create the objects and link the tables you need to use to import this data. Leave everthing else out.

 

[Fifer]

Thanks,

this is attempt number three. Have deleted the module and renamed the type field to action maybe this will work. I dont get how the dat is not going into the table but when you run the delete query it says I am about to delete 191934 rows from the specified table. This being the import cenceo table, but wheres the data???? Also when I run the append query it says it will append 0 rows. could there be a problem with the table?

any clues where to look


cheers greatly

 

[neileg]

If you open the tbl_Import Cenceo, what does the record counter say?

You could import a more restricted set of data, and post the database here. I'll see if I can see the data (assuming there's nothing too sensitive).

 

[Fifer]

it doesnt register anything and only shows one blank line. so where is all the data that the delete query wants to delete??? This is so bizarre and frustrating.

I am going to run the data via excel for each day and see if I can at least paste it over, until I can get my append query to work.