Hi,

My client has 2 messy linked Access databases that I need to use for a .NET 2.0 web application.

I have created a 3rd database (TransferSystem) and linked into it the tables I require for my application from the existing 2 databases.

I then created a few queries that use the linked tables. I propose to use LLBL to bring these queries into my application as "New Entities Mapped On Views".

When I try to refresh the catalog so that I can add the entities/views created, I get the error in the subject and the following extra information:

-----[Core exception]--------------------
   at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
   at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
   at SD.LLBLGen.Pro.DBDrivers.Access.AccessCatalogRetriever.RetrieveSchemas(String connectionString, IDBCatalog catalogToRetrieve, SchemaFetchFlags fetchFlags)
   at SD.LLBLGen.Pro.DBDrivers.Access.AccessDBDriver.PopulateSelectedCatalogs(Hashtable callBacks, Hashtable connectionElements, SchemaFetchFlags fetchFlags, Hashtable properties)
   at SD.LLBLGen.Pro.Gui.Forms.MainWindow.RefreshCatalogs(Boolean unattended, Hashtable catalogsToRefresh)
   at SD.LLBLGen.Pro.Gui.Forms.MainWindow.OnProjectExplorerCatalogRefreshClicked(Object sender, CatalogRefreshEventArgs e)
   at SD.LLBLGen.Pro.Gui.Forms.ProjectExplorer._menuItemCatalogRefreshUnattended_Click(Object sender, EventArgs e)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Also it says that the source is Microsoft Jet Database Engine

I am using the latest version of V2.0.0.0 (july 6th 2007)

Is there anything that can be done to fix this?... If required I can supply the relevant databases as the client has already made all the data anonymous.

Many thanks

You could try updating the MDAC objects (http://msdn2.microsoft.com/en-us/data/aa937730.aspx ) THe error code you got is the 'general I don't have a clue what went wrong' error MS reports.

Hi,

I am using Windows XP, MDAC is already part of the operating system so I cant update it

Regards

hplloyd wrote:

Hi,

I am using Windows XP, MDAC is already part of the operating system so I cant update it

Regards

Sure you can, with xp, you get 2.5 I think, but anyway, it's also possible that the .mdb file is simply corrupt.

Hi,

Thanks for your help on this by the way...

When I try to install MDAC 2.8 or MDAC 2.8 SP1 I get an error

"This setup does not support installing on this operating system"... but any ways I am fairly sure I have the latest MDAC.

I have also tried compacting and repairing the database and I still get the same error.

I can open and use the databse in MS Access with no problems

REgards

After messing about some more it appears that LLBL has problems with the relationships set up for linked tables in the source database...

What do I mean

Well, If i link in a table tblCompany from a seperate database and create a view on that then LLBL refreshes the catalog without a problem and I can add the Company View as an entity.

If I then link in a table tblCompanyType which has a relationship set up in the source database to the tblCompany, when I refresh the catalog I get the error.

I cannot remove the relationship as it is required by the source database, however I would be happy for LLBL to ignore the relationship, as I can create the relationships in the LLBL designer for the Entities Mapped On Views I create.

I hope this makes sense.

Uum

I just put together a test to illustrate my point in my last post and this worked (when it should not have done if I was correct) so I am now confused again

I have put the databases I am having problems with at

http://www.saberanalytics.com/downloads/database.zip

if anyone cares to take a look - I am keen for any suggestions.

The database I am trying to process with LLBL is called TransferSystem.mdb, and this database links to the other 2... You will naturally need to use the Linked Table Manager to refresh the links depending on where you put them.

Also, the database PropertyPartnerShips database.mdb has some links to the Contacts.mdb database - messy I know but this is what the client has given me to work with and I am unable to adjust the structure of these database in any way (except TrasferSystem.mdb which is mine)

Many thanks

We'll examine your database solution a.s.a.p. But first let me ask this: What happens if you use ADO.NET to access your database (the 3rd one, the one with the linked tables), without using LLBLGen Pro? Just to make sure if it's an LLBLGen Pro issue, or an ADO.NET issue.

Regarding

What happens if you use ADO.NET to access your database (the 3rd one, the one with the linked tables), without using LLBLGen Pro? Just to make sure if it's an LLBLGen Pro issue, or an ADO.NET issue.

I have created a simple app that uses the AccessDataSource Control and it all works fine

Regards

hplloyd wrote:

Regarding

What happens if you use ADO.NET to access your database (the 3rd one, the one with the linked tables), without using LLBLGen Pro? Just to make sure if it's an LLBLGen Pro issue, or an ADO.NET issue.

I have created a simple app that uses the AccessDataSource Control and it all works fine

Regards

Yes, but that doesn't use the GetOleDbSchemaTable() call on the OleDbConnection class I think. The Access driver uses that call to obtain meta-data which is where the problem occurs...

Are you saying that I can't use LLBL Gen on my Access Database because the issue is something to do with the Jet Driver and thus outside your control?

Can you think of any work around that I could use, or am I going to have to develop my application without LLBL? I hope there is because I have used LLBL so many times now that I cant remember how NOT to use it !!

hplloyd wrote:

Are you saying that I can't use LLBL Gen on my Access Database because the issue is something to do with the Jet Driver and thus outside your control?

Yes, I have no idea what's causing this other than that the error is inside the GetOleDbSchematable call.

Can you think of any work around that I could use, or am I going to have to develop my application without LLBL? I hope there is because I have used LLBL so many times now that I cant remember how NOT to use it !!

Not without rewriting the driver.

These 3 calls are made:

DataTable uniqueConstraints = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Table_Constraints, new object[] {null, null, null, null, null, null, "UNIQUE"});

// and
DataTable ucFields = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Constraint_Column_Usage, new object[] {null, null, currentTable.TableName, null, null, null, null});

// and
DataTable fkConstraints = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] {null, null, null, null, null, null});

This is normal .net code which should work on access. 'connection' is an open OleDbConnection object. It might be the links between the access db's is the problem.