Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 transactions with attached database

Author  Topic 

luisf
Starting Member

23 Posts

Posted - 2005-02-22 : 10:36:46
Im working with VC++, and an app that connects to a SQL-Server DB via DAO (MFC dao classes). Below the code is shown.
The problem is that SQL-Server hangs (for about 2 minutes) and app finally notifies "ODBC -- call failed".
This does not happen if transaction is no created ( or I close it right before the second open).
It basically:
1st. executes an insertion, which throws a trigger that updates a second table.
2nd. Queries for the new value in the table.

******************
CDaoDatabase daodatabase;
daodatabase.Open( SZ_MDB_SPEC_VINCULADA );
// this is a mdb which has attached tables
...
CDaoQueryDef daoquerydef( &daodatabase ) ;
daoquerydef.Create();
daoquerydef.SetConnect( "ODBC;DRIVER=SQL Server;SERVER=LFRE;APP=Microsoft Office XP;WSID=LFRE;DATABASE=testODBCAccessvinculada;Trusted_Connection=Yes" );

daodatabase.m_pWorkspace->BeginTrans();

strSQL = "insert into tabla (columna1, columna2) values ( '99', '99' )";
daoquerydef.SetReturnsRecords( false );
daoquerydef.SetSQL(strSQL.c_str());
daoquerydef.Execute( dbSQLPassThrough );

//daodatabase.m_pWorkspace->CommitTrans();
// if this line is present it does not hang, but I need to be in a transaction...

strSQL = "SELECT ID from IDENT_CURRENT_TABLA";
CDaoRecordset daorecordset(&daodatabase);
daorecordset.Open( dbOpenSnapshot, strSQL.c_str() ); // hangs in here
daorecordset.MoveFirst();
...
******************

What Im not doing OK?
Thanks in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-22 : 11:10:54
Pass-through queries have no concept of a DAO transaction. DAO has no idea what is happening in your pass-through query and cannot roll-back the transaction. If you want to deal with transactions, you need to connect directly to SQL Server. You should be able to do this using DAO's OpenConnection(). Have you even tried it yet?

- Jeff
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-22 : 11:32:31
Or, (and I know you're gonna hate me for saying this) how about using ADO instead of DAO? DAO does NOT work well with SQL Server, period. Believe me, I've tried it more times than I can count.

You are spending a lot of time pursuing a DAO solution that will either never work, or be far less efficient than one using ADO.
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-02-23 : 05:14:02
Thanyou jeff...
Then assuming I have something like this:
DAO->BeginTransaction
DAO->Connection1 operations
DAO->Connection2 operations
DAO->CommitT

Where Conection1 is related to a mdb, and connection2 is to a sql-server DB.
Is this OK? Does the transacction affect to both groups of operations?

And Rovolk, dont worry, I'm not going to hate you, I'm doing already !!! ;P
Anyway, in this case, would advantages would I get? Wouldn't I need passthrough queries? Can they be handled within a transaction?...

Thanks again, hated-men
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-02-23 : 06:14:22
Sumarizing:
-passthrough queries cannot be handled by transactions
-insertions into a table wich is attached to a external source can only be performed by passthrough queries

=>That means (a general conclusion and very important): in general (if not readonly) you cannot handle transactions in a mdb whose tables are attached to external data sources.
1. Mainly, DBs has to be updated
2. Mainly, DBs operations has to be protected in transactions
3. Passthrough queries are needed for updating linked tables
4. Passthrough queries cannot be (properly) used in transactions

=> Mainly you cannot use linked tables !?!?! Amazing

Where is the fault in the sentences?!?! (and the solution)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-23 : 07:10:21
>> Where is the fault in the sentences?!?! (and the solution)

I give up ... Good luck out there. Someday you will learn a great lesson: when people take the time to give you advice, you should LISTEN CAREFULLY TO ALL OF IT (don't ignore and/or skim over certain parts) and most importantly, consider FOLLOWING THAT ADVICE.

Nothing we can say or do will be able to help you until you decide to learn it for yourself.

- Jeff
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-02-23 : 10:15:05
From the very begining I asked about "general limitations of linked tables"...
"Mainly you cannot use linked tables" is a very general limitation, isn't it?
I've not read anything saying something like that or like the sumary posted.
In order to make decisions I need to know constraints... well, now that I've tested all the stuff about this I got the constraints...

In brief, do you agree with the summary posted?

Im learned by my self, and, of course, all things said by anyone are very useful, specially if reasoned out. Its not so fine just to hear "better do it that other way", it is ever useful, but I needed strong reasons, apart from "I had problems doing in another way".

In any case, thanks to the discussions I've directed my own tests and reached the conclusions.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-23 : 11:00:00
1) linked tables and pass through queries have NOTHING in common -- other than the fact that they both connect to external datasources. But in a given MDB file, they have nothing to do with each other. Pass-Though Queries are just connecting to some datasource and executing a command against it; they have nothing to do with any linked tables that might exist in your Access DB. Does this make sense?

2) Linked tables CAN be updated as long as you have permissions to do so, and the table has a primary key defined. I've done this many times. (if you don't have a PK defined, you have much bigger problems than we can help with ... you have not answered any questions we have asked you yet, but I'll try again anyway: Do your linked tables have primary keys?)



- Jeff
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-02-24 : 06:33:39
quote:
Originally posted by jsmith8858

1) linked tables and pass through queries have NOTHING in common -- other than the fact that they both connect to external datasources. But in a given MDB file, they have nothing to do with each other. Pass-Though Queries are just connecting to some datasource and executing a command against it; they have nothing to do with any linked tables that might exist in your Access DB. Does this make sense?


Well, I understand that; just to tinge, they have in common that passthrough queries are the way to obtain somethings that cannot be done by linked tables.

quote:

2) Linked tables CAN be updated as long as you have permissions to do so, and the table has a primary key defined. I've done this many times. (if you don't have a PK defined, you have much bigger problems than we can help with ... you have not answered any questions we have asked you yet, but I'll try again anyway: Do your linked tables have primary keys?)


Thats the case in one of the tables. But even when the tables has PK, I cannot open the CDaoTableDef for linked table.


CDaoDatabase daodatabase;
daodatabase.Open( SZ_MDB_SPEC_VINCULADA ); // mdb with linked tables
CDaoTableDef daotabledefTabla( &daodatabase );
daotabledefTabla.Open("tabla");
CDaoRecordset daorecordsetSelectTabla( &daodatabase );
daorecordsetSelectTabla.Open( &daotabledefTabla ); // exception!
daorecordsetSelectTabla.MoveFirst();
cout << "daorecordsetSelectTabla CanUpdate: " << daorecordsetSelectTabla.CanUpdate() << endl;

Throws an exception "Invalid Operation" in the line marked "exception!".
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-24 : 10:01:47
I posted something, then deleted it, because I wanted to test for myself. So I am dusting of VC++ and tyring this for myself.

Questions (please answer):

1) When you first open up MS Access, and click on the linked table, does it prompt for log-on information?

2) In Access, can you open the linked table(s) in question and update them manually?

3) You understand that no matter what you do, if you link to a table w/o a primary key, Access cannot update it, right?

by the way:

>>Throws an exception "Invalid Operation" in the line marked "exception!"

Throwing an Exception does not mean something cannot be updated! It means your code is throwing an Exception! there's an error, you must fix it! If it executed succesfully (w/o any errors) and the output indicated that CanUpdate() was FALSE, then that would mean that you cannot update the recordset. Does this make any sense whatsoever?
(please answer this as well if you can)

- Jeff
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-24 : 10:12:37
(before reading this, make sure you don't miss the questions in my previous post)

I had no problem updating a linked table using VC++/MFC/DAO; I opened the recordset using a SQL Statement instead of a tableDef. At first, it would not let me edit the table because it had an identity column, and DAO gave me a very clear error message indicating that I needed to open the recordset with the "dbSeeChanges" option due to the identity column. (not sure if that was part of your problem or not; I noticed that you did not set any flags or options in your sample).

Once I did that, it worked fine. here is the code:


CDaoDatabase d;
d.Open("c:\\AccessDev\\Test97.mdb");
CDaoRecordset r(&d);
r.Open(dbOpenDynaset,"SELECT * FROM dbo_Employees", dbSeeChanges);

if (r.CanUpdate())
this->MessageBox("Can update!");
else
this->MessageBox("CANNOT update!");

r.Close();
d.Close();

In the above sample, I have a linked table in my Access MDB file to a SQL Server using NT authentication, and it is linked to the table "Employees" in the sample "Northwind" database.

- Jeff
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-25 : 09:05:47
Hello .... ? Did this help .... ?



- Jeff
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-02-28 : 05:04:45
Argh! I lost the reply because a a power cut !!!!

quote:

1) When you first open up MS Access, and click on the linked table, does it prompt for log-on information?


No. Well, I created linked tables setting using TrustedConnection.

quote:

2) In Access, can you open the linked table(s) in question and update them manually?


Yes.

quote:

3) You understand that no matter what you do, if you link to a table w/o a primary key, Access cannot update it, right?


Yes, but, seems to be not exact. Because I CAN insert a register from access to a linked table that has not PK.
quote:

by the way:

>>Throws an exception "Invalid Operation" in the line marked "exception!"

Throwing an Exception does not mean something cannot be updated! It means your code is throwing an Exception! there's an error, you must fix it! If it executed succesfully (w/o any errors) and the output indicated that CanUpdate() was FALSE, then that would mean that you cannot update the recordset. Does this make any sense whatsoever?
(please answer this as well if you can)


The exception means I doing something wrong -as could be to open a dao table when is a linked table-.
---------------------
Well I got all running but it seems that important diferences are in DAO3.5 and DAO3.6
One code workd for DAO3.6 (opening as basically you have post), but hangs in 3.5 (even SQL-Server)
Another works for DAO3.5, but is far more complex (based on pasthrough), and in one computer works and in other throws an exception (I think it simple to solve, because is explicative -is not doing the passthrough, and posibly because I did not explicitly set the dbSQLPassThrough -In my tests it worked in passthrough mode as the SetConnect is used-.
----------------------
Im putting on hold this topic as I get it running, but I'll do it better in the future.

Thankyou very much for the effort.

Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-02 : 09:06:39
quote:

Well I got all running but it seems that important diferences are in DAO3.5 and DAO3.6
One code workd for DAO3.6 (opening as basically you have post), but hangs in 3.5 (even SQL-Server)
Another works for DAO3.5, but is far more complex (based on pasthrough), and in one computer works and in other throws an exception (I think it simple to solve, because is explicative -is not doing the passthrough, and posibly because I did not explicitly set the dbSQLPassThrough -In my tests it worked in passthrough mode as the SetConnect is used-.



Yeah, it's too bad that neither Rob or myself thought to suggest to you that you'd have trouble with DAO. Our bad for not bringing it up ... Rob, we need to get on the ball next time!

Good luck, let us know if you need more assistance.

- Jeff
Go to Top of Page
   

- Advertisement -