Relink tables with ADO.net

Well I am still stuck in this never ending conversion from vb6 to vb.net and have encountered yet another problem in doing something in ADO.net that was very simple in DAO. We have a front end DB that has a link to all of the various back end DB's. All DB's are done in MS Access 2000. Now this frontend gets refreshed on occasion and the links are reestablished to the backends. This is done with the following DAO code:


Code:

So in DAO this refreshes the link to these tables now because of converting we are attempting to do the same thing in ADO.net. And unfortunately the only answer I have found online is that this is not possible in ADO.net and to keep using the com components for DAO or upgrade to the com component for ADO. So instead of going bald over this I need your help again. Is something like this possible in ADO.net? Any help would be appreciated.

I think the bulk of your problem is in understanding that the fundamental concepts behind ADO.NET are completely different from DAO. For example, when working with DAO, you have to maintain an open connection while you work with recordsets.

In ADO.NET, however, once you load your data to a Dataset, you work with the Dataset and close your connect. The data continues to reside in the Dataset, with no link back to the database. You can display and manipulate the data in the Dataset, and then when you are ready, you re-connect back to the databases and update the data based on the changes made to the dataset. What I've described sounds complicated, but can really be done in just a few lines of code.

With the little you've given, I can't really offer a guideline of what you need to do. But basically, switching from DAO to ADO.NET will involve a complete re-write of your code to work with the databases--it won't be a simple one-to-one conversion. However, you will likely find the functionality much better when you switch.

There are a number of sources on ADO.NET available that you can research, including the FAQs here that will help you get a good grasp of the fundamentals of ADO.NET.

Ok let me see if I can try to explain this a little bit better. Our database is actually used by multiple different companies and there backends each reside in a different location and sometimes on a different drive. To simplify having multiple databases and more relationships than access can handle, we created a database that all it has in it is a link to each table in all of the backends. When we install the program we place a control database in the program files folder for our program. This control database contains things like user names and private keys, but also contains the path to the location of the databases. To avoid having to customize our link database for each client we perform the afore mentioned DAO relinking to point the links to the correct location. More than anything else this allows us to do one line of configuration when we set things up instead of several hours of setup for each company. This also simplifies things whenever we make changes to the backends because the code handles all of the linkage.

As far as a complete rewrite of the logic to perform the ADO instructions we have already done that. There are just a few loose ends that we have not been able to figure out how to do in ADO.net. This unfortunately is a big loose end that we have actually saved for one of the last things to finish up for the Data Access portion of our program. So I hope this helps some in figuring out what we are trying to accomplish. Once again any help is appreciated.

Here's something I've used in the past. It uses ADOX to create the table links.


Code:

Of course, these were links to text files. I think you can easily change the Link Provider String above to "DATABASE=" and the path to your database that you want to link to.

Hopefully that gives you an idea.

Yeah we don't have a problem opening the databases the actual links are inside of the frontend database itself. So these links get updated and refreshed to the new location of the databases. This is all done before I ever try and open a connection to get the data. The type of link that I am referring to is in MS Access you can go to file->Get External Data->Link Tables. This creates essentially a pointer to the backend table that I want. So we have actually done this for over 300 tables. Now I now what you are thinking, if we have this many tables and this much data we should just move to sqlserver but the problem with that is the companies we do work for are Not-For-Profits and don't have the budget to get sqlserver. So these DBs since we can't guarantee what directory or drive they will be on we created this frontend link that we know exactly where it is. So the problem is that through code we have to repoint the links to a new location. Because we are moving from a non .Net environment to a .Net environment we are attempting to get Rid of all of our com dll's otherwise I would just keep using DAO to do the relinking process since DAO has a simple method of doing it. As was shown in my first post.

So what I am trying to figure out is if there is a way to relink (change the pointer if you will) inside of this front end using ADO.net or if not with ADO.net then at least be able to update them using straight VB.net code instead of having to rely on the older technology. Now I have found that I can update the links using a dataset and using the frontend system table MSysObjects and changing the path of the Database field, but I then need to refresh the database. Or would this not be necessary in ADO.net since I don't have a continual connection to the database like I did in DAO.

Hopefully this helps explain things a little bit better and any help is greatly appreciated.

If you can update the system tables with your dataset method, then that's going to be the only way to do it without using some sort of COM object (DAO, ADOX). I'm not sure what you mean about refreshing the database. I assume you mean doing the actual update?

In DAO after updating the link through the connection string we always had to call tdf.RefreshLink() so the tabledef(schema in ADO.net) would be refreshed. But since ADO.net is a disconnected technology I probably don't have to call any kind of refresh anymore. I guess I'll have to play around with it and see.

Quote:

Now I now what you are thinking, if we have this many tables and this much data we should just move to sqlserver but the problem with that is the companies we do work for are Not-For-Profits and don't have the budget to get sqlserver

Have you thought of using SQLExpress - it is free - you could then create linked servers to each database

Just a thought in case the MSysObjects method didnt work. Good luck:thumb:

We hadn't considered SQLExpress as an option and may look into it in the near future, but for now we are stuck with access.

So to update you I can get acces to the MSysObjects table and can see everything that I need to change in the database portion but when I go to update the table I get the following exception: "Operation must use an updateable query." Everything that I have seen online says that this is a permission issue. So I modified the MSysTable to give me full access to the table. I also verified that I have access to read an write to the Database. And I am still getting the error. Any Ideas???? I posted the code I am using down below.


Code:

let me know what you think. Thanks again.

OK I have played with this all weekend with no success. In theory altering the MSysObjects table should work. But for some reason I cannot get it to accept an update from the program. So does anyone know if the Microsoft System tables cannot be updated via code? Looking at the table security everything should be fine and I should be able to modify them but can't. Let me know if you have any ideas. Thanks.