December 27, 2006 at 6:00 am
I recently converted an Access database to an Access project file (.adp) and for the most part the conversion was successfull. The problem is that some of the queries from the original Access (.mdb) database were using linked tables from another Access (.mdb) database. From all that I can find there seems to be no way to link tables into the .adp project unless the user has a local SQL-Server install or MSDE. As most users do not have this type of setup I am wondering if there are any work arounds that someone has come accross or any suggestions to get around this issue. Thanks for any help with this!
December 27, 2006 at 8:05 am
You can create a view on SQL Server to view tables from other databases. You will be able to read and write to that view from the adp.
December 27, 2006 at 8:37 am
Perry, could you explain, even briefly, how to write a view on SQL Server from a table in an Access database.
Thanks!
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
December 27, 2006 at 9:51 am
Hi Perry,
I would also like to know how to create a view in SQL to a table in Access. The ony things I can find to do that would be to create a Linked Server. The problem with that is that is appears that the .mdb file has to be on the same server as SQL and that is usually not the case. I did create a Linked server to the .mdb database and I could not connect. I was getting some initialization errors and could not find any good documentation on what the Provider property should be set to (if at all). This should be an easy thing to do but so far has been one dead-end after another.
Thanks,
Gary
December 27, 2006 at 6:07 pm
I found this great write-up on Linked Servers here. After reading it, and thinking that where it is asking for the path to the database, I could put any path that is accessible within the network. Therefore, networked drives or network addresses (\\servername\folder) should be ok here. And from my testing, it looks like that works. So, if the Access database is not on the same drive as the SQL database, as long as it is on a drive that the network can get to, you should be able to get to it and use it for your linked server. After linking an Access database as the linked server on a network drive, I then tested getting to the records from a table in that database with this syntax (covered in the referenced tutorial above): SELECT * FROM servername...tablename. Those three periods between the name of your linked server and the table name are important.
Hope this helps! It has really helped me,
Vic
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
December 28, 2006 at 7:50 am
I made the assumption that when you coverted the mdb to an adp that you did both databases. Now that I understand the issue here is an approach. Move the table(s) from the mdb to SQL Server and then make an ODBC connection for the mdb. Then you could do a view for the adp.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply