Get Access & SQL Server To Co-operate

[DJanton]

Can you solve this one?

I'm having very unusual problems with not being able to get VB code in access to work with linked SQL Server tables.

For one, I'm able to link all the SQL Server tables to Access DB without a problem. I even tested each link by viewing data in each linked table in Access. However, when I'm running VB code in Access form and executing Access query that attempts to delete a record from a single linked table, I get an error 'could not find linked table'.

In another approach; I tried to program a connection via a connect string (ADODB.connection), this results with a similar error "could not find linked table; Kernel". My Kernel is of the 32 bit version.

Would anyone have an idea of what the problem is, or how to go about finding the solution for this?

Anyones input or help is much appreciated.

Thank You Very Much.

 

[FoFa]

Do you have an installed SqlServer driver?

 

[crosmill]

FoFa, do you mean a different driver than the ODBC that comes with Access?

Daniel, most people are pretty active on this site and will find your posts

 

[FoFa]

Check your installed MDAC version on client and SqlServer, sometimes MDAC differance cause unpredictable results (I know MS says it shouldn't, but we have had that problem). What version of Access are you using?

 

[DJanton]

Could not find linked table

Hello FoFa,

Thank you for the tip to check the MDAC version. My boss is looking into it.

As far as Access version it's ACCESS 2000.

I'll know shortly if things have improved.

Thank You.

 

[DJanton]

From what I can see (within the administrative Tools) the ODBC DSN is defined for the DB and the driver is SQLSRV32.dll. I ran a connectivity test directly on the DSN and it shows perfect results.

Thank You.

 

[FoFa]

Could this be maybe a permissions problem on the Sql table? When you connect via ADO, are you specifing a Sql login, or using windoz authentication?

 

[DJanton]

FoFa, I ran an MDAC checker and was able to correct most of the version mismatch problems except for one for mkjet.dll file.

RE ADO, I'm simply connecting via the ADODB connect string; Connect string = "Provider=SQLOLEDB.1;Data Source=HOSQL1;Initial Catalog=ItrackTest;User ID=sa;"

Next, con.execute method runs with an SQL string to delete 1
row from Project table on ItrackTest DB. It appears that this works just fine. However, there is a trigger on this table that deletes a bunch of rows in other tables in the same DB + a cascading trigger to delete rows from another DB. That is when the problem occurs and I get a message "Invalid object RebarTest.XCOPlan" - like it does not recognize that there is a DB named RebarTest and table XCOPlan.

Does this mean I need to establish a separate connection for DB affected by the trigger? I find this very odd.

Thank You.

 

[FoFa]

Does the trigger work outside of this application, or is the trigger part of this application (I.E. developed at the same time)? Usually a reference to a different DB (but on the same server) is DB.dbo.TABLE, I don't see the "dbo" in your reference, but maybe something is different with your setup. If the delete is a cascading delete setup on your Sqlserver, and you are accessing the server using SA (Which is a bad practice I might add), permissions should not be a problem. But the error you are getting is telling you it can't find it where it can see.

 

[DJanton]

Cascading Delete - ADODB Connection

FoFa,

Thank you for your help.

The cascading delete is part of the SQL Table in ItrackTest DB.

I know what you mean about the sa connection but I'm not the DB manager here, I'm just following orders if you know what I mean.

I do use full names as far as table name reference.

Thanks. Dan.