Insert Into...the statement that wouldn´t work..

Er det med vil at der til sidst står """ i stedet for )"  ?

Prøv evt. også at fjerne VALUES. Det har før virket for mig uden VALUES (http://www.eksperten.dk/spm/428608)

DoCmd.RunSQL "INSERT INTO Medlem_stamdata(Medlem_stamdata.grad, Medlem_stamdata.dato, Medlem_stamdata.bemarkninger, Medlem_stamdata.Fornavn, Medlem_stamdata.Efternavn, Medlem_stamdata.Adresse, Medlem_stamdata.PostNr, Medlem_stamdata.F_dato, Medlem_stamdata.Telefon, Medlem_stamdata.Mobil, Medlem_stamdata.Email, Medlem_stamdata.Hold, Medlem_stamdata.Medlems_status, Medlem_stamdata.Tidligere_gradueringer) SELECT grad, dato, bemarkninger, Fornavn, Efternavn, Adresse, PostNr, F_dato, Telefon, Mobil, Email, Hold, Medlems_status, Tidligere_gradueringer FROM tbl_import"

Snowball

virker stadig ikke... den mener stadig der mangler i komma..har access en max string length for for DoCmd.RunSQL?

min sætning ser således ud...not working though

DoCmd.RunSQL "INSERT INTO Medlem_stamdata ( grad, dato, bemarkninger, Fornavn, Efternavn, Adresse, PostNr, F_dato, Telefon, Mobil, Email, Hold, Medlems_status, Tidligere_gradueringer )SELECT (Medlem_stamdata.grad, Medlem_stamdata.dato, Medlem_stamdata.bemarkninger, Medlem_stamdata.Fornavn, Medlem_stamdata.Efternavn, Medlem_stamdata.Adresse, Medlem_stamdata.PostNr, Medlem_stamdata.F_dato, Medlem_stamdata.Telefon, Medlem_stamdata.Mobil, Medlem_stamdata.Email, Medlem_stamdata.Hold, Medlem_stamdata.Medlems_status, Medlem_stamdata.Tidligere_gradueringer) FROM (tbl_import)"

Om det er RunSQL der ikke kan håndtere SQL'en kan du jo teste ved at ligge selve din SQL sætning ind i en query i Access og så prøve at køre den derfra.

Snowball

det har jeg lige prøvet det får jeg en fejl
"Medlems databe for GSK angav 0 felt(er) til Null pga. en typekonverteringsfejl, og 1 post(er) blev ikke tilføjet til tabellen pga. nøglefejl, 0 post(er) pga. låse fejl og 0 post(er) pga. fejl i valideringsregler."

der er kun 1 post i den tabel jeg leger med, så der er intet der kommer igennem.. nøglefejl...hvad mener den..skal der være en relation mellem tmp tabellen og "den rigtige" for det ville jeg gerne undgå

DoCmd.RunSQL "INSERT INTO Medlem_stamdata ( grad, dato, bemarkninger, Fornavn, Efternavn, Adresse, PostNr, F_dato, Telefon, Mobil, Email, Hold, Medlems_status, Tidligere_gradueringer )SELECT (Medlem_stamdata.grad, Medlem_stamdata.dato, Medlem_stamdata.bemarkninger, Medlem_stamdata.Fornavn, Medlem_stamdata.Efternavn, Medlem_stamdata.Adresse, Medlem_stamdata.PostNr, Medlem_stamdata.F_dato, Medlem_stamdata.Telefon, Medlem_stamdata.Mobil, Medlem_stamdata.Email, Medlem_stamdata.Hold, Medlem_stamdata.Medlems_status, Medlem_stamdata.Tidligere_gradueringer FROM tbl_import)"

pops up a syntax error...

I have a screen dump of the access error when I try to test it in query builder..if anyone wants to have a quick look

DoCmd.RunSQL

"INSERT INTO Medlem_stamdata
( grad, dato, bemarkninger, Fornavn, Efternavn, Adresse, PostNr, F_dato, Telefon, Mobil, Email, Hold, Medlems_status, Tidligere_gradueringer )

SELECT Medlem_stamdata.grad, Medlem_stamdata.dato, Medlem_stamdata.bemarkninger, Medlem_stamdata.Fornavn, Medlem_stamdata.Efternavn, Medlem_stamdata.Adresse, Medlem_stamdata.PostNr, Medlem_stamdata.F_dato, Medlem_stamdata.Telefon, Medlem_stamdata.Mobil, Medlem_stamdata.Email, Medlem_stamdata.Hold, Medlem_stamdata.Medlems_status, Medlem_stamdata.Tidligere_gradueringer FROM tbl_import"

This one actualy enters a post in my table, but it insert a name matching the first record and provides it with an autonumber value more than twice the value it should get.... but it´s way better than before...still not correct though..

vb.... you dont need to keep rejecting answers! Wait until we are finished and then decide if you want to reject or not.

I dont understand the last comment!
"This one actualy enters a post in my table ..."

Thats what it is supposed to do, so your problem is solved :o)
(Insert Into...the statement that wouldn´t work..)

It does insert now!

So what is your next problem?
You are selecting ALL records from Medlem_stamdata table and INSERTing them into the same table! And BECAUSE you dont have any other constraints other than th eprimary key (ID) then this is accepted as a new records. Nothing wrong with that, unless of course it sint what you want but that is NOT mentioned in your question!

Terry. your post seems to "copy" the first post of Medlem_stamdata (which it the table I Insert Into) and gives it an autonumber. not following the last record in Medlem_stamdata. It does not inset the post(s) form tbl_import which is where the imported data is waiting for me

Well terry I agree that is inserts not...but not from tbl_import which was my problem from the beginning Im not selecting all the records from tbl_import because some values are not needed, maybe I described it wrong...The short version is I want the above mentioned fields to be inserted in Medlems_stamdata FROM tbl_import

I hope u dont mind, but Im not going to accept until I get it to work..I increased the points though

Yes I know, but what else do you expect to get from your SQL?


change ALL field in your SELECT to use tbl_import instead of Medlem_stamdata


LOOK at the name you are using in the select!!!!

SELECT Medlem_stamdata ....
it should be
SELECT tbl_import....

I must admit I find it strange it actually works as it is!

DoCmd.RunSQL

"INSERT INTO Medlem_stamdata
( grad, dato, bemarkninger, Fornavn, Efternavn, Adresse, PostNr, F_dato, Telefon, Mobil, Email, Hold, Medlems_status, Tidligere_gradueringer )

SELECT grad, dato, bemarkninger, Fornavn, Efternavn, Adresse, PostNr, F_dato, Telefon, Mobil, Email, Hold, Medlems_status, Tidligere_gradueringer FROM tbl_import"

tried that...this is where I get 1 post was not inserted due to key violation

try sending the dB to NOSPAMeksperten@santhell.dkNOSPAM

remove NOSPAM

You must make sure that the values in tbl_import DO NOT try to insert duplicate unique key values otherwise they WILL fail!

me too..it realy wierd, I tried the solution with no table names in both the insert and select lines..no luck there... look... I´ll raise it to a hundred point If you´ll take a look at it..I can mail it to you

there can be dublicate..I have a field named grad..and a lot can have the same value there...

but it is not a unique key

This is my email NOSPAMeksperten@santhell.dkNOSPAM

but you must remove NOSPAM

have you sent it?

This is the problem!

The field Grad has a relationship on another table (Baelte_billeder) which means that ONLY values from this table can be inserted into Medlem_stamdata.Grad

and the value which you are trying to INSERT is "2.Mon" which DOES NOT exist in the table.

So you have two options as far as I can see!

1: You make sure that the data you are to import DOES EXIST.
2: You INSERT any new "grad" values into Baelte_billeder first.

I can´t believe the answer was RIGHT under my nose..that´s the only place I didnt think of looking, because i was SO sure I wrote it like that...
thanx mate

its always nice to have fresh eyes look at a problem :o)

and thanks too!

right on that :-)
by the way, I trust you have deleted the DB i mailed you, since it contains personal information...

You bet I have!
If I kept all of the dBs I get, I would need at least 500 GB :o)

LOL