ms-access: double linked table

It is possible, I did it myself but for different reasons. It was a stop gap while half the applications in a group were migrated and half stayed on access for a few months but we still needed to run reports that spanned both applications.

I will say that there was a marked performance hit using this method and I would echo the recommendation of using the excellent access auto FE updater to ease this kind of issue in the future

Sure HansUp, it might be a bit of an edge case but this is my situation.

I had a number of back end MDB files for a group of applications, I needed to have all of this information in one database so I could do reporting using business objects that needs all of the tables under one “connection” (in this case the connection is to this MDB that just has linked tables to the real back ends).

Some of these applications got migrated to SQL server before others including some common shared tables. In some situations the chain went Front End -> Back End -> SQL server. It does take a performance hit and I’m much happier now that the migration is fully complete.

Oh and I don’t link using DSNs I use a DSN-less setup with a bit of code to re point the tables to different serves for testing/production

I created a database, BE.mdb, which contains a single table, Books.

Next I created another database, FE.mdb, which contains a link to the Books table in BE.mdb

Then, I opened BE.mdb and replaced Books with an ODBC link to a table in a PostGreSQL database.

Does that description correspond to what you want to accomplish?

If so, I don't see how it can work because when I open FE.mdb again and try to open the Books linked table, Access complains "The Microsoft Jet database engine cannot find the input table or query 'Books'".

I think Access' database engine is looking for a native Jet table (or query) named Books ... which is what existed when the link was created. However, the Books link target was changed from a Jet table to an ODBC link, so the Jet database engine can't find what it is looking for.

The only way I can find to make it work is to change the link definition in FE.mdb ... but I thought that's what you wanted to avoid.

@beth: please reread the question. question is can the access backend have the ODBC

i am a girl 2010-09-22 22:07:43

yes, an Access mdb can include tables linked via ODBC to a table on a SQL Server database. If you want, you can populate your existing Access table with data from the connected SQL Server table in a batch process and not have to add ODBC connections to the clients.

Beth 2010-09-23 14:06:06

@beth please reread the question again

i am a girl 2010-09-27 01:45:48

@jenny, I've read it and don't understand it the way you do. Please add more explanatory info, including the question you think we're answering and how your question is different.

Beth 2010-09-28 14:49:48

@beth I think jenny has a FE.mdb with links to Jet tables in BE.mdb. Then jenny wants to replace the BE.mdb tables with links to SQL Server ... and have the FE.mdb links usable without modifications to FE.mdb. Are you saying that is possible?

HansUp 2010-09-29 15:20:03

@HansUp nope. The links in FE.mdb need to be refreshed. She can replace the be.mdb tables with links to a table on a SQL Server (change the source location,) but the FE.mdb links to the old source location must be refreshed, which is not a big deal.

Beth 2010-09-29 15:32:55

@beth, please read the question AGAIN. AGAIN!!!!!!!!!!!!!!

i am a girl 2010-09-29 16:51:47

@jenny, it looks the same as the last few times I read it. Have you edited it? How else could I possibly gain more understanding of your problem? Your question is too unclear for me to respond in a helpful way, and right now, I'm not interested in helping you. You don't deserve it.

Beth 2010-09-29 17:23:54

@beth no prob, just please be more careful next time

i am a girl 2010-09-29 18:49:55

@Kevin Can you describe the method you used for the "double linking"? Thanks.

HansUp 2010-09-23 15:10:22

HansUp, I have edited my answer to show some more detail

Kevin Ross 2010-09-24 07:07:26

Thanks, Kevin, but I'm still missing something. When I open FE.mdb to try to create a link to a BE.mdb object, the only choices Access offers are the native Jet tables. None of the links which exist in BE.mdb are available.

HansUp 2010-09-27 04:09:08

I also tried the Transferdatabase method, but could not find an option for "database type" which represents an existing link. So I'm trying to ask what *method* you used to create a link to a linked table in another database.

HansUp 2010-09-27 04:12:45

@HansUp, as long as the name is the same (linking to a SQL Server table adds the dbo,) Access should be fine with it, but you will have to update the link to point to the new source location.

Beth 2010-09-28 14:47:19

i am a girl 2010-09-28 16:41:18

ansaurus

tags:

views:

answers:

ms-access: double linked table

related questions