Linking Tables

[Douglas J. Steele]

You can link a single front-end to as many back-ends as you like.

However, each linked table must have a unique name. If backend1 and backend2
both have tables named tblPartyPlanner, you'll have to give one of them a
different name in your front-end. In other words, that SQL can only update
the table in a single back-end.

What exactly are you hoping to do?

 

[DS]

This is very confusing to me so please bear with me.

I have a split database.
I want to write my records to a second database.
The question is can I link 2 database backends to one front end and
inorder for the info to go into the 2nd database do I heve to have the
code on the front end to do it? Such as...

Dim PartySQL As String
DoCmd.SetWarnings False
PartySQL = "UPDATE tblPartyPlanner " & _
"SET tblPartyPlanner.PartyDone = 0 " & _
"WHERE tblPartyPlanner.PartyID = " & _
Me.TxtPartyID
DoCmd.RunSQL (PartySQL)
DoCmd.SetWarnings True

Will this Update both database backends or just the original backend and
leave backend 2 untouched.

If it doesn't update both how might I do this?

I'm a little confused over this linking thing. On what you can and
cannot do, what are the downsides of this? Is corruption more likely
since the connection is always on?

Thanks
DS

 

[DS]

You can link a single front-end to as many back-ends as you like.

However, each linked table must have a unique name. If backend1 and backend2
both have tables named tblPartyPlanner, you'll have to give one of them a
different name in your front-end. In other words, that SQL can only update
the table in a single back-end.

What exactly are you hoping to do?

Thanks Douglas.
I'm trying to get an exact copy of a transaction saved to another
computer in another database. The problem with making a backup is that
it's ok for the end of the day but I need this backup or copy during the
day, while the database is running. I think the only way to get a copy
a record while running would be this way, because shuting down the
databse is out of the question. Perhaps I should have used a true
client/server db but at this point I'm so far in...so I thought by
linking this could be done without having to record every single line!!!

So I guess this is it ???

Dim PartySQL As String
DoCmd.SetWarnings False
PartySQL = "UPDATE tblPartyPlanner " & _
"SET tblPartyPlanner.PartyDone = 0 " & _
"WHERE tblPartyPlanner.PartyID = " & _
Me.TxtPartyID
DoCmd.RunSQL (PartySQL)

Dim Party2SQL As String
DoCmd.SetWarnings False
Party2SQL = "UPDATE tblPartyPlanner2 " & _
"SET tblPartyPlanner2.PartyDone = 0 " & _
"WHERE tblPartyPlanner2.PartyID = " & _
Me.TxtPartyID
DoCmd.RunSQL (Party2SQL)

DoCmd.SetWarnings True DoCmd.SetWarnings True

I would do this and link the tables or linking no longer neccasary?
If I don't link then I would code the second part differently?

Thanks,
(Confused as all heck!)
DS

 

[Douglas J. Steele]

Thanks Douglas.
I'm trying to get an exact copy of a transaction saved to another computer
in another database. The problem with making a backup is that it's ok for
the end of the day but I need this backup or copy during the day, while
the database is running. I think the only way to get a copy a record
while running would be this way, because shuting down the databse is out
of the question. Perhaps I should have used a true client/server db but
at this point I'm so far in...so I thought by linking this could be done
without having to record every single line!!!

You might want to look into replication.

Start by looking at
http://support.microsoft.com/?id=190766 - white paper
http://support.microsoft.com/?id=282977 - FAQ

Also see
http://www.trigeminal.com - excellent replication site
http://support.microsoft.com/support/access/content/repl/replication.asp