How to write insert into SQL in VB (with password

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to write insert into SQL in VB (with password

Author

Topic

loacker5
Starting Member

10 Posts

Posted - 2004-11-10 : 03:40:26

hi..
i'm using access as my backend dbase but is having a trouble inserting record to another access database with password.. can somebody help me on this. the code runs like this..

"INSERT INTO DestionationTable IN DestinationDbase ??? select * from SourceTable"

I think it should include something like ";pwd=password" but don't know the exact syntax for it.

any help would be appreciated..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 07:40:51

use Linked Tables; add a linked table in your source database to link a table in your destination database.

Then just append to that linked table -- the data will end up in the destination database. read up on linked tables in Access help.

- Jeff

loacker5
Starting Member

10 Posts

Posted - 2004-11-11 : 04:58:25

Thanks for the reply jeff.. but i create new database on a daily basis.. like 11-11-04.mdb, 11-12-04.mdb so on.. that's why i wouldn't be able to create a link manually to my source table.. i've used this code on a no password database and it works.. "INSERT INTO TargetTable IN TargetDBase SELECT * FROM SourceTable".. I think the syntax should be something like "INSERT INTO TargeTable IN TargetDBase, ";pwd=password" SELECT * FROM SourceTable".. but don't know exactly the right syntax is.... help...

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-11 : 08:56:31

I haven't connect to access using passwords with DAO

I always use passwords to connect to the SQL Server with ADO and
I always set the password as part of the connection string.
(see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41747)

I guess that if you need two different passwords for source and destination you'll also need
two connections and you would not be able to use a statement like

INSERT INTO TargetTable IN TargetDBase SELECT * FROM SourceTable

Instead you'll need a recordset for source and another for destination and pass the values from
one recordset to the other


recDest!field1=recSource!field1
...
recDest.update

or if both source and destination have the same format then

for i = 0 to recSource.fields.count-1
     recDest.fields(i)=recSource.fields(i)
next
recDest.update

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 09:17:45

this was a tricky one, but here's your solution:


INSERT INTO Test ( [Value] ) IN '' [MS Access;database=c:\accessdev\test2.mdb;PWD=jeff]
SELECT "hey" AS Expr1;

that should work for you.

- Jeff

loacker5
Starting Member

10 Posts

Posted - 2004-11-12 : 00:51:00

Yes its possible TUENTY. Thanks though for the reply and Many thanks to jeff.. it did work! though i have edited the syntax a little into

"INSERT INTO Test([Value]) IN ''[c:\accessdev\test2.mdb;PWD=jeff]
SELECT "hey" AS Expr1;"

Don't know why It just needed the two single qoute ('') after "IN".

Thanks again Jeff! I really appreciate it.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 09:29:03

this will certainly simplify some stuff I need to do too, if it work with fox too

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources