Problem with SQL

[sacramento]

Hi:

I have this expression:

db.Execute "INSERT INTO " & value & " IN '" & App.Path & "\progaves.mdb ' SELECT criacoes.* FROM criacoes

"

and,because the database have a password I need to put something like that in the expresion,but I had try a few things and I can not make the expresion work

Set db = Workspaces(0).OpenDatabase(App.Path & "\progaves.mdb", [B]False, False, ";pwd=agaporni[/B]")

Any help please
Thanks

[Hack]

What error are you getting?

[sacramento]

hi:

the error is:

"COmpile error:Wrong number of arguments or invalid property assignement"

in this expression i had try:

db.Execute "INSERT INTO " & value & " IN '" & App.Path & "\progaves.mdb", False, False, ";pwd=agaporni" ' SELECT criacoes.* FROM criacoes"

Thanks

[sacramento]

Hi Gurus of SQL...no ideias for this problem?

Thanks

[sbudhisatrio]

if u already open a database (with password) and set them into a 'db', u don't need to set password for any other process : just type db.execute "SQL statement....."

[LinXG]

Could you please explain what you are trying to accomplish with this statement....

db.Execute "INSERT INTO " & value & " IN '" & App.Path & "\progaves.mdb", False, False, ";pwd=agaporni" ' SELECT criacoes.* FROM criacoes"

It doesn't make any sense to me.

[Al42]

It doesn't make any sense to the computer either - that's why it's throwing an error.

[sacramento]

I try to transfer recordsets from a database to annother...this statement is one of the proccess!

Thanks

[LinXG]

Hi sacramento;

Based on what you've said I understand that you want to read data from a table in Database A and transfer that data into another table in database B.

My response is based on this understanding.

1) So, you start by opening a recordset against a table or query in Database A

2) Next you open a connection to Database B.

3) Then you loop through the recordset and insert a new record into a table in Database B. This is where I assume the db.Execute statement that you're using comes into play.

4) When you're done close the recordset and close the connection.

I can only make a partial comment on your use of the db.Execute statement because I have never seen it structured the way you have it written.

The INSERT INTO part is not correct. It should look like this

INSERT INTO Table_In_DataBase_B (FIELD1, FIELD2, FIELD3....) VALUES (VALUE1, VALUE2, VALUE3...)

If you need a concrete code sample of how to do this let me know and I'll see what I can come up with.

[randem]

sacramento,

How on earth did you come up with that code anyways??? There are no examples like that anywhere...

[sacramento]

Hi Guys:

THis statement work fine without password on Database:

db.Execute "INSERT INTO " & value & " IN '" & App.Path & "\progaves.mdb ' SELECT criacoes.* FROM criacoes

Randem,this statement work with me on my code and make that I want...any problem with the statement?

I just only want add, in this statement,the way to open my database with password...only this guys ...I think the problem it was the syntax!

LinXG - Yes you are right...that is just I want to do!

sbudhisatrio - Maybe it was the best way!

[si_the_geek]

That syntax is valid for Access (it has been on the forums several times), but I have no idea how you could put password details into it - annoyingly I have never found any proper documentation for that syntax!

Does your "current" database have a password on it? If not, you could connect to the other DB to run the SQL the "opposite" way.

If it does have a password, you will probably need to do the kind of thing that LinXG suggested.

[sacramento]

Hi:

yes I think the best way is open the database before I use the statement,and forget the password in the statement i had put here in the forum.

Anyway thanks to all for your comments

[LinXG]

So if I substitute these values into your statement it will work?

value = someTable
App.Path = c:\someDir

"INSERT INTO someTable IN 'c:\someDir\progaves.mdb' SELECT criacoes.* FROM criacoes"

Interesting I tried it and it works. You learn something new everyday.

[sacramento]

yes work

[si_the_geek]

So if I substitute these values into your statement it will work?

Close, you need single quotes around the file name, like this:

"INSERT INTO someTable IN 'c:\someDir\progaves.mdb' SELECT criacoes.* FROM criacoes"

It's a nice method, but the documentation doesnt seem to exist! (unlike other databases, such as SQL Server which does the same using qualified names like: ServerName.DatabaseName.OwnerName.TableName ).

[sacramento]

Si_the_Geek you are right

[LinXG]

Hi Si;

I took them out figuring that I was reading the statement wrong but when I went and created the test tables I found that you do need them. I was editing them back in as I noticed your last post.

This is the first example of this useage I have ever seen. This forum is a real learning experience.

[si_the_geek]

Woooot!!

After searching every few months for the last couple of years, I have finally found a bit more info - still no proper documentation, but I think it is enough for this situation!


Rather than using the syntax "tablename in 'c:\database.mdb'", it seems it is also valid to use this syntax: "[MS Access;DATABASE=c:\database.mdb].[tablename]"

No obvious major gains as yet, but it helped me find a little more info - this version seems to be a basic connection string - so you can put the password info in, eg:
"[MS Access;DATABASE=c:\database.mdb;Jet OLEDB:Database Password=mypassword;].[tablename]"


So hopefully, this will work:

db.Execute "INSERT INTO [MS Access;DATABASE=" & App.Path & "\progaves.mdb;Jet OLEDB:Database Password=agaporni;].[" & value & "]  SELECT criacoes.* FROM criacoes"

[sacramento]

Thanks for your search but still not work...

run time error 3001

[si_the_geek]

Darn...

what was the error message?

[sacramento]

invalid statement

[si_the_geek]

Ok, I've done a few tests and it seems the password needs to be done like this:

db.Execute "INSERT INTO [MS Access;DATABASE=" & App.Path & "\progaves.mdb;[u]Uid=admin;Pwd=agaporni;[/u]].[" & value & "]  SELECT criacoes.* FROM criacoes"

If the field names are different, you need to use aliases in the Select clause like this:

SELECT Field1 as FirstField, Field2 as SecondField

..and if the table doesnt exist already, you need to use a Select Into, like this:

db.Execute "SELECT criacoes.* INTO [MS Access;DATABASE=" & App.Path & "\progaves.mdb;Uid=admin;Pwd=agaporni;].[" & value & "]  FROM criacoes"

[randem]

Ok so that seems to be a hold over from the Dbase days. Why would you want to use such a convoluted syntax. It will just confuse everyone that looks at it later, expecially since there is no proper documentation on the syntax.

[Simply Me]

i dont know if the link in my signature works for you. It's about connecting to the database.

[si_the_geek]

Ok so that seems to be a hold over from the Dbase days. Why would you want to use such a convoluted syntax. It will just confuse everyone that looks at it later, expecially since there is no proper documentation on the syntax.

I agree with your points, however depending on the situation the speed increase of using this method (as opposed to loading a recordset from one DB, and adding the data to the other) may be a requirement of this program - it's easy enough to write a comment in the code to briefly explain the syntax.

[LinXG]

Hi Si;

Since you've done some reseacrch on this, do you know if this would work to bring data from, for example, a MySQL DB to a Postgres DB?


Thanks

[si_the_geek]

Nope, this is a different "flavour" of SQL to MySQL or Postgres.

I would assume that this applies to databases that use the Jet engine (Access/Text/Excel/...), but may be limited to Access only.

For other DBMS's you will need to use relevant SQL or tools, such as DTS for SQL Server to get non-SQL Server data.