INSERT in another DB 1

[Olavxxx]

Hi,

INSERT INTO navn IN '\\path\to\sendtil.mdb'
SELECT navn
FROM tbl_navn
WHERE skal_digitaliseres=True And sendt_til_dig=False;

I use this for inserting data in db2, from db1.
This query works perfectly!

But:
I have to insert in another table, in db2 too.
This is why I would need to retrieve the insert_id (autonumber) from the previous query!

How do I find this?

I'm used to use PHP/mySQL
I dont think it's a good sollution to run a query and retrieve the ID with the highest number.

The thing is, that people might use the db2, while db1 is inserting in db2. Also, the first query should only insert, if it is not stored there already!

Maybe I have to explain some more:
db1 has one table for customer and order
if order is digital, the database should query db2 and check if the customer is stored there in db2.table1

if user is stored there, simply insert order in db2.table2
if user is not stored, insert customer in db2.table1, retrieve the insert_id and use that insert_id for inserting order in db2.table2

Is this possible? I think it should be, as it's very easy to do in mySQL.

ps. it's non profit! I'm doing service for the community. (preservation of books, etc.) and this should copy orders which want digital results (scanned, etc.).

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[RoyVidar]

Hei Olav Alexander!

I think to make this work, you'll probably need to execute this on the same connection, and use the identity. For instance something like this:

[tt]dim cn as adodb.connection
dim rs as adodb.recordset
dim strsql as string
set cn=new adodb.connection
cn.connectionstring="provider=Microsoft.jet.oledb.4.0;" & _
"data source=yourpathto\sendtil.mdb"
cn.open
cn.execute "INSERT INTO navn " & _
"SELECT navn FROM tbl_navn " & _
"WHERE skal_digitaliseres=True And " & _
"sendt_til_dig=False"
set rs=cn.execute("select @@identity")
debug.print rs.fields(0).value[/tt]

- typed not tested

Roy-Vidar

 

[Olavxxx]

Thank you for fast feedback.

I tried implementing this code, but it seems that when using this code, it does not find the source table.. (it self)..

I thought it might be naming convensions in my test-tables, to I renamed some.

I got the query working thru a macro:

INSERT INTO kunde IN '\\path\to\sendtil.mdb' SELECT navn AS kundenavn FROM tbl_navn WHERE skal_digitaliseres=True And sendt_til_dig=False;

but, when using the code above, it seems it does not find it self for querying! I tried appending the database name:

cn.Execute "INSERT INTO kunde " & _
           "SELECT navn AS kundenavn FROM sendfra.tbl_navn " & _
           "WHERE skal_digitaliseres=True And " & _
           "sendt_til_dig=False;"

but, then it says it cannot find the file and gives me a path, which is the folder outside of the folder where the database is!

I might be missing something obvious here.. I'll keep on trying and try to google some more.

I'll give you a star for your tip.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[RoyVidar]

Overlooked that, sorry.

Could you link to the external table(s) in stead? Then you could just use currentproject.connection. I think, to reliably retrieve the last autonumber (thru identity) the insert and retrieval need to be performed on the same connection.

Roy-Vidar

 

[Olavxxx]

hmm..

I cant say I completely follow here.
Maybe my brain is better after some lunch?

DB1 has to insert in DB2.field1

After altering the above code, it seems that it is unable to query it's own table..

eg. db1 is unable to query db1.table

I get the error "cannot find in-data table or in-data-query tbl_navn" (might be some other names in English, I have Norwegian Access here)..

(tbl_navn is the "local" table, eg in the same db which I inserted this code)

I'm off to lunch, will check back later.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[RoyVidar]

Norwegian is no problem;-)

You have two databases db1 and db2.

You wan't to append a record in db2 with info from db1. No prob, several methods, the one you used initially does the job nicely.

Challenge - return the last autonumber in db2. To return the last autonumber reliably, one need to execute the insert and retrieve the autonumber on the same connection. The concept does not contain any problem, one just perform the insert, and retrieve the autonumber using a (the same) connection to db2.

What makes a problem, is that when doing an append on a connection to another database, that connection is not aware of of the contents of the current database (it doesnt doesn't see the local table, as you reported).

Now - what to do? Three approaches to try:

1 - I think it shouold be possible to make ADO aware of two connections, and perform such, but I don't know how - perhaps some web search my help, or search here?

2 - make Access/ADO aware of the external table(s) by linking them to db1 (Fil | Hent eksterne data | Koble/link?). Then ADO will know each table, and it could be executed with something like this, perhaps?

[tt]currentproject.connection.execute "your insert"
set rs=currentproject.connection.execute("select @@identity")[/tt]

3 - open a recordset based on sendfra-tabellen din;-) and try something like this perhaps

[tt]strSql="insert into kunde (navn) " & _
"values ('" & rs("kundenavn") & "')"
currentproject.connection.execute strsql
set rs=currentproject.connection.execute("select @@identity")[/tt]

Roy-Vidar

 

[RoyVidar]

Ouch - the last sample should have been executed on the "other" connection, with the value (kundenavn) from a "local" recordset:

[tt]strSql="insert into kunde (navn) " & _
"values ('" & rs("kundenavn") & "')"
cn.execute strsql
set rs=cn.execute("select @@identity")[/tt]

Roy-Vidar

 

[Olavxxx]

Once again: Thank you very much for your comments and suggestions! I'll get right on testing this.

I've already tested some with the "eksterne data", but from the other end..
I'll try using that first, since I guess that one is far less complicated than the others, as then access will handle the connection by itself.

I'll post back and tell how it went, when I have any progression.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Olavxxx]

Thank you VERY much

I now got it working here, kindof.. lol..
I know enough for now, I think, as I also have to run two other queries, but that will be easy compared to what you came up with.

However, I found one strange thing to affect the insert:
When debug.print, the insert was not made!
After changing it to MsgBox (rs.Fields(0).Value), it worked fine!

Seems when using the debug.print, it did not insert, but still the insert id went away..
Maybe I can make the book "1 million strange things with MS Access".

I've already encountered a couple of nasty things, where access modifies queries, etc. after they are saved. After modification, they do not work any more (SQL queries).

One query would only work in Macro, or in VBA. If it was typed in the Query part, Access figured out it should "style" the query, untill it no longer worked.

Once again, thanks!

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[Olavxxx]

btw. forgot to post the working code:

id.SetFocus

CurrentProject.Connection.Execute "INSERT INTO kunde " & _
                "SELECT navn AS kundenavn FROM tbl_navn " & _
                "WHERE id = " & id.Text & " AND " & _
                "skal_digitaliseres=True And sendt_til_dig=False;"
Set rs = CurrentProject.Connection.Execute("select @@identity")

(with the external tables)

Now I just have to polish it a bit, run one query first, to see if it already is in the db, also I need one query to update fields, etc.

I also have to do some VBA programming, to convert name into firstname and lastname..

I hope someone else will find this post, when they encounter the problem(s) I had.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster