Linking an Access table to SQL Server

Question

Post reply

Linking an Access table to SQL Server

guerillaunit

SSCarpal Tunnel

Points: 4249
More actions
January 25, 2012 at 7:42 am

#263248

Is there any way to link an Access table on a shared drive to a SQL Server DB?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Do you mean can you open an Access table from sql server?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

Not sure what the limitiations are but since you have SQL Server, the best solution would be to import the access database into a SQL database, and connect the access database directly to hit the SQL server table(s)

Any reaosn you have to use Access???

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 3

If the table needs to remain in Access (not recommended, btw), then you should be able to use OPENROWSET or some other similar T-SQL command to find the data you need.

Reasons to move the table to SQL Server and link the Access to the SQL database include data availability, avoiding Access corruption issues (more common than SQL corruption issues), and a consistent backup strategy that be auto-implemented rather painlessly as opposed to an Access backup strategy.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

sroumel SSC Veteran Points: 262 More actions · Answer 4

You can also create a linked server under the server objects in Management studio and then query your access tables