Situation:

     data entered into an SQL database (MS SQL Server 7.0; AKA db1) needs to

be copied

     to an MS-Access .mdb file (daily uploads of new records will be done)



Challenge:

     MS-Access .mdb file resides on hardware (AKA SrvrB) in a different

domain and is

     "owned" by a different group from the SQL database (AKA SrvrA)



Current Solution Direction:

     Set up MSDE or MS SQL Server 7.0 on SrvrB with (local) MS-Access .mdb

file connected

     connected as "linked server" (AKA lnk-file):

               sp_addlinkedserver

                     'lnk-file',

                     'Access 97',

                     'Microsoft.Jet.OLEDB.4.0', 

                     'D:\db1.mdb'



     Develop a VB6 application with ADO to open connection to each server

and copy

     record data (example code below)



Frustration:

     In testing if this will work, I have run up against a roadblock (not

the first I have

     experienced with this project, nor, I expect, the last...). Everything

works fine until

     the code attempts to update the record in the linked server recordset.

At that point

     there is an error.



     In the "real" .mdb file (my test/development copy of the production

file), the error

     is:

              Run-Time Error '-2147467259 (80004005)'

              [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object

name

              'lnk-file...table 1'



     In a "test" .mdb file (a simple MS-Access database), the error is:

              Run-Time Error '-2147418113 (8000ffff)'

              Query cannot be updated because the FROM clause is not a

single

              simple table name.



The second error message led me to article Q174640 in the Microsoft

KnowledgeBase.

However, that article focuses on using multiple tables, not anything about

linked servers.



I have no idea why I am getting a different message from the simple

database. I created

that database to test whether it was the space in the table name that was

causing the

problem (it apparently was not- with the table name = table2, I get the same

8000ffff

error message).



I would appreciate any ideas, even if you can tell me that what I am trying

to do with the

linked server is not possible (if you can tell me how to get a web server

and/or an SQL

server to "connect" to a net share in a different domain I would be very

interested!).



Thanks!

.Tim

Tim D. Hammer

Tim.Hammer@u...

Test & Reliability Tools Development, OSG/RMS&T

Xerox Corporation



'********** EXAMPLE CODE **************

Dim cnSrvrA as ADODB.Connection

Dim cnSrvrB as ADODB.Connection

Dim rsSrvrA as ADODB.Recordset

Dim rsSrvrB as ADODB.Recordset



Set cnSrvrA = new ADODB.Connection

Set cnSrvrB = new ADODB.Connection

Set rsSrvrA = new ADODB.Recordset

Set rsSrvrB = new ADODB.Recordset



cnSrvrA.ConnectionString = "DRIVER=SQL Server;SERVER=SrvrA" + _

     ";UID=test;PWD=testing;DATABASE=db1"

cnSrvrB.ConnectionString = "DRIVER=SQL

Server;SERVER=SrvrB;UID=testB;PWD=testing"



cnSrvrA.Open

cnSrvrB.Open



rsSrvrA.Open "SELECT {list of fieldnames to be copied in each record}" + _

     " FROM [table1] WHERE uploadstamp IS NULL", cnSrvrA, _

     adOpenForwardOnly, adLockPessimistic, adCmdText



rsSrvrB.Open "SELECT {list of fieldnames to be copied in each record}" + _

     " FROM [lnk-file]...[table 1] WHERE 1 = 2", cnSrvrB, adOpenForwardOnly,

_

     adLockPessimistic, adCmdText



rsSrvrB.AddNew

rsSrvrA.MoveFirst



rsSrvrB("field1").Value = rsSrvrA("field1").Value

' repeat for each field to be copied



rsSrvrB.Update

rsSrvrA("uploadstamp") = Now()

rsSrvrA.Update

' move to next rsSrvrA record and loop for each record to be copied...



rsSrvrA.Close

Set rsSrvrA = Nothing

cnSrvrA.Close

Set cnSrvrA = Nothing



rsSrvrB.Close

Set rsSrvrB = Nothing

cnSrvrB.Close

Set cnSrvrB = Nothing