Using Access Database with linked tables in VB.NET

James Bartlett 21 Reputation points
2021-01-12T17:55:09.3+00:00

In my VS2019 VB.Net programme I am using an Access 2003 Database which has tables linked to another Access 2003 Database.

The database it is linked to is updated periodically by another programme over which I have no control.

My database has tables that contain information about the PA System my programme is controlling. The linked tables in the other database are updated by the software in the PA System itself to give error events and other information which I want to use.

The problem is how do I refresh the tables that are linked in my database so that I get any updated or added record from the other database?

Is there a better way of doing it?

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,710 questions
0 comments
Accepted answer
  1. Karen Payne MVP 35,401 Reputation points
    2021-01-12T18:44:28.297+00:00

    Hello @James Bartlett

    Try using a OdbcCommand and OdbcConnection, setting up the SELECT where in the FROM this is the current database while in the example below the JOIN points to the other database. 

    SELECT   
	c.LastName, m.MonthName   
FROM   
	Clients c   
	INNER JOIN [;Database=C:\PathToOtherDatabase\someDatabase.mdb].[Months] m ON c.RenewalMonth = m.MonthID
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Albert Kallal 5,246 Reputation points
    2021-01-15T01:56:30.863+00:00

    I don’t really quite grasp the issue here.

    A linked table points to live data. So any changes – even in real time will be reflected by your software opening that database.

    Linked tables are ONLY pointers in Access. They do NOT hold data, nor do they need to be “updated”.

    In fact, using the oleDB providers against linked tables in Access even works for SQL server tables, FoxPro/dbase or just about anything else that Access linked to.

    But, Access does NOT store data, nor needs to wait or “sync” or be updated in this context.

    From YOUR point of view, you are opening that table, and any data changes, updates etc. should be seen by your software in real time (in fact your software can’t tell the difference).

    There is ONLY one exception to this rule. (But it would be a HUGE detail you would have left out).

    When Access uses linked tables to SharePoint tables? In that case Access does sync in real time. And in fact if Access has no network connection, these SharePoint tables continue to operation in the “local” cache data store. When network connectivity is restored, then the “sync” of data now occurs again. This is a great feature of Access + SharePoint. So you can send say laptops out in the field – use the application. When you get back to network land, then the data is synced for you automatic. If you have a live connection, then data is quite much synced in real time.

    Of course the solution in .net wold be to NOT use the linked data source in Access, but simply point YOUR .net software to the actual data source in question (in this case SharePoint).

    But, other than SharePoint tables, your software does not and in fact can’t even tell the difference between a table in that Access database, and that of you using + opening a linked table in that database. There is NO data store in Access and no need to “wait” or determine if Access been updated or has the latest data.

    So, linked tables in Access are in fact just pointers to some other “live” data source.

    It is possible perhaps the table(s) in question are NOT linked tables, but in fact the data is being imported from that other data source. In that case? Then your .net software should NOT use the tables in Access, but simply point your .net application to the actual “live” data sources you want to use. (No need to use nor have access as a go-between here).

    So, from what you explained so far?

    I can only think that the table(s) in question are NOT linked tables, or perhaps we talking about SharePoint tables – but that would be quite a spectacular detail to have left out of your post.

    If they are SharePoint (linked) tables in Access? Then simply point your .net code at the SharePoint site and consume the live data that way – thus by-passing Access and its SharePoint “sync” process.

    Regards,
    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.