Merging Records from one table to another on different databases

Hey ppl, hope u guys can help me out in my situation.

Scenario : I have 2 password protected databases (Access 2002), the
situation is a local copy and a live copy on the server. Users on laptops
can work offline out of the office and have records stored in a tmp table
that has the exact same structure as the live copy on the server. When the
users are back in the office they need to synchonize the data.

Issue: Replication doesnt work on password protected databases. It prompts
me that if i try to create a replica.
So i decided to write code instead (VB). Whats the best way to update all
the records from one database to the other. When i use the update statement
to put the records into a different physical database that is password
protected, i get an invalid password error. How can i specify the password
in my query ?

i.e

"UPDATE [LIVE TABLE] INNER JOIN [TABLE OFFLINE] ON [LIVE TABLE].[REF NO] =
[TABLE OFFLINE].[REF NO] in 'c:\temp\sample.mdb' SET [LIVE TABLE].[REF NO]
= [TABLE OFFLINE].[ref no], [LIVE TABLE].[DATE] = [TABLE OFFLINE].[DATE],
[LIVE TABLE].INITIAL1 = [TABLE OFFLINE].[INITIAL1] ......

Can anyone suggest a better way maybe ? im trying to avoid writing code to
connect to the database with the password and then doing a loop to add the
records in.

Regards,
Mike




---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004
Hey ppl, hope u guys can help me out in my situation.

Scenario : I have 2 password protected databases (Access 2002), the
situation is a local copy and a live copy on the server. Users on laptops
can work offline out of the office and have records stored in a tmp table
that has the exact same structure as the live copy on the server. When the
users are back in the office they need to synchonize the data.

Issue: Replication doesnt work on password protected databases. It prompts
me that if i try to create a replica.
So i decided to write code instead (VB). Whats the best way to update all
the records from one database to the other. When i use the update statement
to put the records into a different physical database that is password
protected, i get an invalid password error. How can i specify the password
in my query ?

i.e

"UPDATE [LIVE TABLE] INNER JOIN [TABLE OFFLINE] ON [LIVE TABLE].[REF NO] =
[TABLE OFFLINE].[REF NO] in 'c:\temp\sample.mdb' SET [LIVE TABLE].[REF NO]
= [TABLE OFFLINE].[ref no], [LIVE TABLE].[DATE] = [TABLE OFFLINE].[DATE],
[LIVE TABLE].INITIAL1 = [TABLE OFFLINE].[INITIAL1] ......

Can anyone suggest a better way maybe ? im trying to avoid writing code to
connect to the database with the password and then doing a loop to add the
records in.

Regards,
Mike




---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004

Hi Mike,

How can i specify the password in my query ?

Open the query in design view. Click on View Properties to display the properties. Make sure
that you see "Query Properties" in the blue title bar of the properties dialog (as opposed to
"Field Properties"). Look for a property called "Source Connect Str". Click into this property
setting and press F1 to bring up context sensitive help for this property.

Tom
___________________________________________

"Mike Smith" wrote in message ...

Hey ppl, hope u guys can help me out in my situation.

Scenario : I have 2 password protected databases (Access 2002), the
situation is a local copy and a live copy on the server. Users on laptops
can work offline out of the office and have records stored in a tmp table
that has the exact same structure as the live copy on the server. When the
users are back in the office they need to synchonize the data.

Issue: Replication doesnt work on password protected databases. It prompts
me that if i try to create a replica.
So i decided to write code instead (VB). Whats the best way to update all
the records from one database to the other. When i use the update statement
to put the records into a different physical database that is password
protected, i get an invalid password error. How can i specify the password
in my query ?

i.e

"UPDATE [LIVE TABLE] INNER JOIN [TABLE OFFLINE] ON [LIVE TABLE].[REF NO] =
[TABLE OFFLINE].[REF NO] in 'c:\temp\sample.mdb' SET [LIVE TABLE].[REF NO]
= [TABLE OFFLINE].[ref no], [LIVE TABLE].[DATE] = [TABLE OFFLINE].[DATE],
[LIVE TABLE].INITIAL1 = [TABLE OFFLINE].[INITIAL1] ......

Can anyone suggest a better way maybe ? im trying to avoid writing code to
connect to the database with the password and then doing a loop to add the
records in.

Regards,
Mike




---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004
Hey ppl, hope u guys can help me out in my situation.

Scenario : I have 2 password protected databases (Access 2002), the
situation is a local copy and a live copy on the server. Users on laptops
can work offline out of the office and have records stored in a tmp table
that has the exact same structure as the live copy on the server. When the
users are back in the office they need to synchonize the data.

Issue: Replication doesnt work on password protected databases. It prompts
me that if i try to create a replica.
So i decided to write code instead (VB). Whats the best way to update all
the records from one database to the other. When i use the update statement
to put the records into a different physical database that is password
protected, i get an invalid password error. How can i specify the password
in my query ?

i.e

"UPDATE [LIVE TABLE] INNER JOIN [TABLE OFFLINE] ON [LIVE TABLE].[REF NO] =
[TABLE OFFLINE].[REF NO] in 'c:\temp\sample.mdb' SET [LIVE TABLE].[REF NO]
= [TABLE OFFLINE].[ref no], [LIVE TABLE].[DATE] = [TABLE OFFLINE].[DATE],
[LIVE TABLE].INITIAL1 = [TABLE OFFLINE].[INITIAL1] ......

Can anyone suggest a better way maybe ? im trying to avoid writing code to
connect to the database with the password and then doing a loop to add the
records in.

Regards,
Mike




---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004

Thanks for reading my post...
it seems the SourceconnectStr actually adds some properties after the IN
clause.. so i tried setting it as:

IN 'c:\temp\databasename.mdb'[PWD=mypassword;]

got an error staing it could not find installable ISAM. Recalled ive seen
this error when it doesnt know what database its trying to connect to. So i
do the following instead :

IN 'c:\temp\databasename.mdb'[MS Access;PWD=mypassword;]

Now i get the NOT A VALID PASSWORD error message as i had it before the
connectStr parameters... Im very sure the password im typing in is correct.

Any clues ?


--
Regards,
Mike
http://bikesh.europe.webmatrixhosting.net
http://www.planetsourcecode.com (search keyword: phoetus)

"Tom Wickerath" wrote in message
...
Hi Mike,

How can i specify the password in my query ?

Open the query in design view. Click on View Properties to display the
properties. Make sure
that you see "Query Properties" in the blue title bar of the properties
dialog (as opposed to
"Field Properties"). Look for a property called "Source Connect Str".
Click into this property
setting and press F1 to bring up context sensitive help for this property.

Tom
___________________________________________

"Mike Smith" wrote in message
...

Hey ppl, hope u guys can help me out in my situation.

Scenario : I have 2 password protected databases (Access 2002), the
situation is a local copy and a live copy on the server. Users on laptops
can work offline out of the office and have records stored in a tmp table
that has the exact same structure as the live copy on the server. When the
users are back in the office they need to synchonize the data.

Issue: Replication doesnt work on password protected databases. It prompts
me that if i try to create a replica.
So i decided to write code instead (VB). Whats the best way to update all
the records from one database to the other. When i use the update
statement
to put the records into a different physical database that is password
protected, i get an invalid password error. How can i specify the password
in my query ?

i.e

"UPDATE [LIVE TABLE] INNER JOIN [TABLE OFFLINE] ON [LIVE TABLE].[REF NO] =
[TABLE OFFLINE].[REF NO] in 'c:\temp\sample.mdb' SET [LIVE TABLE].[REF
NO]
= [TABLE OFFLINE].[ref no], [LIVE TABLE].[DATE] = [TABLE OFFLINE].[DATE],
[LIVE TABLE].INITIAL1 = [TABLE OFFLINE].[INITIAL1] ......

Can anyone suggest a better way maybe ? im trying to avoid writing code
to
connect to the database with the password and then doing a loop to add the
records in.

Regards,
Mike




---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004
Hey ppl, hope u guys can help me out in my situation.

Scenario : I have 2 password protected databases (Access 2002), the
situation is a local copy and a live copy on the server. Users on laptops
can work offline out of the office and have records stored in a tmp table
that has the exact same structure as the live copy on the server. When the
users are back in the office they need to synchonize the data.

Issue: Replication doesnt work on password protected databases. It prompts
me that if i try to create a replica.
So i decided to write code instead (VB). Whats the best way to update all
the records from one database to the other. When i use the update
statement
to put the records into a different physical database that is password
protected, i get an invalid password error. How can i specify the password
in my query ?

i.e

"UPDATE [LIVE TABLE] INNER JOIN [TABLE OFFLINE] ON [LIVE TABLE].[REF NO] =
[TABLE OFFLINE].[REF NO] in 'c:\temp\sample.mdb' SET [LIVE TABLE].[REF
NO]
= [TABLE OFFLINE].[ref no], [LIVE TABLE].[DATE] = [TABLE OFFLINE].[DATE],
[LIVE TABLE].INITIAL1 = [TABLE OFFLINE].[INITIAL1] ......

Can anyone suggest a better way maybe ? im trying to avoid writing code
to
connect to the database with the password and then doing a loop to add the
records in.

Regards,
Mike




---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004





---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004

Hi,

Try Klik! DataCompareLib :

http://www.kliksoft.com/?S=2&SS=12

Regards,

Özden Irmak
Klik! Software

"Mike Smith" wrote in message
...
Hey ppl, hope u guys can help me out in my situation.

Scenario : I have 2 password protected databases (Access 2002), the
situation is a local copy and a live copy on the server. Users on laptops
can work offline out of the office and have records stored in a tmp table
that has the exact same structure as the live copy on the server. When the
users are back in the office they need to synchonize the data.

Issue: Replication doesnt work on password protected databases. It prompts
me that if i try to create a replica.
So i decided to write code instead (VB). Whats the best way to update all
the records from one database to the other. When i use the update
statement to put the records into a different physical database that is
password protected, i get an invalid password error. How can i specify the
password in my query ?

i.e

"UPDATE [LIVE TABLE] INNER JOIN [TABLE OFFLINE] ON [LIVE TABLE].[REF NO] =
[TABLE OFFLINE].[REF NO] in 'c:\temp\sample.mdb' SET [LIVE TABLE].[REF
NO] = [TABLE OFFLINE].[ref no], [LIVE TABLE].[DATE] = [TABLE
OFFLINE].[DATE], [LIVE TABLE].INITIAL1 = [TABLE OFFLINE].[INITIAL1] ......

Can anyone suggest a better way maybe ? im trying to avoid writing code
to connect to the database with the password and then doing a loop to add
the records in.

Regards,
Mike




---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004
Hey ppl, hope u guys can help me out in my situation.

Scenario : I have 2 password protected databases (Access 2002), the
situation is a local copy and a live copy on the server. Users on laptops
can work offline out of the office and have records stored in a tmp table
that has the exact same structure as the live copy on the server. When the
users are back in the office they need to synchonize the data.

Issue: Replication doesnt work on password protected databases. It prompts
me that if i try to create a replica.
So i decided to write code instead (VB). Whats the best way to update all
the records from one database to the other. When i use the update
statement to put the records into a different physical database that is
password protected, i get an invalid password error. How can i specify the
password in my query ?

i.e

"UPDATE [LIVE TABLE] INNER JOIN [TABLE OFFLINE] ON [LIVE TABLE].[REF NO] =
[TABLE OFFLINE].[REF NO] in 'c:\temp\sample.mdb' SET [LIVE TABLE].[REF
NO] = [TABLE OFFLINE].[ref no], [LIVE TABLE].[DATE] = [TABLE
OFFLINE].[DATE], [LIVE TABLE].INITIAL1 = [TABLE OFFLINE].[INITIAL1] ......

Can anyone suggest a better way maybe ? im trying to avoid writing code
to connect to the database with the password and then doing a loop to add
the records in.

Regards,
Mike




---
Outgoing mail is certified Virus Free. (well i'd like to think it is.. )
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.775 / Virus Database: 522 - Release Date: 08/10/2004