Merging Records from one table to another on different databases

  • Thread starter Thread starter Mike Smith
  • Start date Start date
M

Mike Smith

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
 
Hi Mike,
How can i specify the password in my query ?
Click to expand...

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
___________________________________________


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
 
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 said:
Hi Mike,
How can i specify the password in my query ?
Click to expand...

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
___________________________________________


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
Click to expand...
 
Hi,

Try Klik! DataCompareLib :

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

Regards,

Özden Irmak
Klik! Software

Mike Smith said:
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
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Record-Locking remains open after closing 0
Modifying Tables (Adding Field) 1
How to export table from outlook into ms access table? 0
Create records automatically in related table? 2
Link to table from Protected Access Database 1
Unique References in Multiple copies of same database 6
Importing records from one table to another 2
Copying table data from one database to another- suggestions? 4

Back
Top