2nd Database

[Douglas J. Steele]

It makes no difference whether the two databases are on the same computer or
not. Whatever approach you had for the case where they were on different
computers should work in this case as well.

The code snippet you've posted will not solve your problem. What it does is
create a linked table for you. The restriction of needing to rename the
tables in the second database will not go away because you used code to
create the linked table versus doing it manually through File | Get External
Data | Link.

 

[Douglas J. Steele]

It makes no difference whether the two databases are on the same computer
or not. Whatever approach you had for the case where they were on
different computers should work in this case as well.

The code snippet you've posted will not solve your problem. What it does
is create a linked table for you. The restriction of needing to rename
the tables in the second database will not go away because you used code
to create the linked table versus doing it manually through File | Get
External Data | Link.

I think this should work....
Dim Test2SQL As String
DoCmd.SetWarnings False
Test2SQL = "UPDATE [;database=\\Backoffice\C:\DB\Cur.mdb].table1 " & _
"SET table1.IDName = ""New1"" " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
DoCmd.Setwarnings True

Provided that a valid reference to the database, yes, it should work.

As was pointed out to you yesterday (by Marsh, I believe), having a colon in
a UNC seems unusual. A UNC is supposed to be the machine name followed by
the share name.

 

[DS]

I write my data to a linked back end.
I also want to write my data to a second db.
The question is if I link this second database back end I'll have to
rename my tables, I'd perfer not to do that. If I don't link, well I'm
not sure of the synatax on that.....the newsgroup did supply code on how
to do it if it's on a different computer which worked quite well, alas
the situation changed so I am back in a corner !!!

Dim TestSQL As String
DoCmd.SetWarnings False
TestSQL = "UPDATE table1 " & _
"SET table1.IDName = ""New1"" " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (TestSQL)

This one is in a different database on the same computer..
Database2.mdb

Dim Test2SQL As String
Test2SQL = "UPDATE table1 " & _
"SET table1.IDName = ""New1"" " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True


I found this code but I'm not sure on how to use it....
I believe its for linking a second databse on the same computer without
changing table names. But how it's used....?


Dim FrontDB As DAO.Database
Dim Lnk As DAO.TableDef
Set.FrontDB = CurrentDB
Set.Lnk = FrontDB.CreateTableDef(TableName)
Lnk.SourceTableName = TableName
Lnk.Connect = ";DATABASE=" & Databasename
FrontDB.TableDefs.Append Lnk

Any help is appreciated. I think this should about cover all of the bases!!!
Thanks
DS

 

[Douglas J. Steele]

Is this better?

[;database=\\Backoffice\DB\Cur.mdb].table1
or this
[;database=C:\Backoffice\DB\Cur.mdb].table1

Sorry, but only you can answer that. You know your setup, we don't.

Does either of them work?

 

[DS]

It makes no difference whether the two databases are on the same computer or
not. Whatever approach you had for the case where they were on different
computers should work in this case as well.

The code snippet you've posted will not solve your problem. What it does is
create a linked table for you. The restriction of needing to rename the
tables in the second database will not go away because you used code to
create the linked table versus doing it manually through File | Get External
Data | Link.

Thanks Douglas...
So can I just update to the second database without having them linked,
then this way I won't have to change the names. If I can what would the
syntax be? Also if I really do get stuck and I have to change names s
there a way to change them so that I can runcode to change them back?
Just barking up a tree here!
Thanks
DS

 

[DS]

It makes no difference whether the two databases are on the same computer or
not. Whatever approach you had for the case where they were on different
computers should work in this case as well.

The code snippet you've posted will not solve your problem. What it does is
create a linked table for you. The restriction of needing to rename the
tables in the second database will not go away because you used code to
create the linked table versus doing it manually through File | Get External
Data | Link.

I think this should work....
Dim Test2SQL As String
DoCmd.SetWarnings False
Test2SQL = "UPDATE [;database=\\Backoffice\C:\DB\Cur.mdb].table1 " & _
"SET table1.IDName = ""New1"" " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
DoCmd.Setwarnings True

DS

 

[DS]

Is this better?

[;database=\\Backoffice\DB\Cur.mdb].table1
or this
[;database=C:\Backoffice\DB\Cur.mdb].table1

Thanks Douglas
DS

 

[DS]

Is this better?

[;database=\\Backoffice\DB\Cur.mdb].table1
or this
[;database=C:\Backoffice\DB\Cur.mdb].table1

Sorry, but only you can answer that. You know your setup, we don't.

Does either of them work?

The second one works!
Thanks
DS