Linked server for data access (1 Viewer)

[jamest85]

Hi:

I am confused by the concept of "using Linked server for data access". (This is the article I am looking at, I don't know what is exactly trying to accomplish: http://www.aspfree.com/c/a/Microsof...d-Microsoft-Access-Server-on-SQL-2005-Server/ )

My questions are:
1. will the database created be an Access .mdb database? or a database in SQL server?
2. If the created database is .mdb, why we need it link to sql server? Does that mean my other application (ie: myAnotherMSApplication.mdb, or myDotNetWebApp.aspx can access the just created Access .mdb from that SQL server?

Thank you very much.

JT

 

[pbaldy]

1) It would be an MDB
2) It would make the MDB data available to SQL Server stored procedures, etc. Yes, other applications could then see the Access data through SQL Server.

 

[jamest85]

1) It would be an MDB
2) It would make the MDB data available to SQL Server stored procedures, etc. Yes, other applications could then see the Access data through SQL Server.

Thank you very much for you quick response.

If I have an Access database, myAccess.mdb, now I link this .mdb to the sql server, so in the sql server can see the myAccess.mdb tables....

Can I do this: in myAccess.mdb, I will treat its tables as if they are located in sql server, I create a global connection string to the sql server, I create a global ADODB connection to the sql, I write my vba code to grab table data from the sql server, not from the .mdb directly.

Is that Ok? Although it seems strange, but I want to totally understand this.

Thanks

JT

 

[pbaldy]

It does sound strange, but I suppose you could do it, yes. On the server side, you'd need to use the appropriate syntax to get data from the linked server. Off the top of my head, I believe it's

SELECT Blah FROM LinkedServerName..AccessTableName

You can verify that looking up linked servers in BOL.

 

[jamest85]

It does sound strange, but I suppose you could do it, yes. On the server side, you'd need to use the appropriate syntax to get data from the linked server. Off the top of my head, I believe it's

SELECT Blah FROM LinkedServerName..AccessTableName

You can verify that looking up linked servers in BOL.

Thanks PAUL