Can I use an application developed with DAO, ADO or RDO with MSSQL 2000?
(I'm migrating the server)
Are there compatibility issues?
Greetings,Yes, you can access SQL Server 2000 using ADO, so an application that can
reference ADO can access SQL Server 2000. That's typically what ADO is used
for. ADO superceeds RDO and DAO, but I believe they can still use the SQL
Server 2000 provider.
"MedioYMedio" <MedioYMedio@.discussions.microsoft.com> wrote in message
news:72CBE14D-5F52-45C9-8CDA-D702B62DA444@.microsoft.com...
> Can I use an application developed with DAO, ADO or RDO with MSSQL 2000?
> (I'm migrating the server)
> Are there compatibility issues?
> Greetings,
Showing posts with label dao. Show all posts
Showing posts with label dao. Show all posts
Saturday, February 25, 2012
Friday, February 24, 2012
DAO, Transactions, SQLServer
Hello,
I have a problem with DAO, Transactions and SQLServer. I want to do a very
simple thing (in VB)!
BeginTrans
Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
'mycounter'", dbSQLPassThrough)
Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
value = Rec(0)
CommitTrans
Note how simple this is. I just want to get a new value for a counter,
UPDATing first in order to make sure each value is returned only once, even
in concurrent environment. This is what you learn in school.
Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
SQLServer using DAO/ODBC.
In SQLServer using DAO/ODBC the code just hangs the entire application at
the SELECT line.
I believe the problem comes from the ODBC SQL Server driver
(2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
are opened (by the driver I believe) and I suspect that the driver sends the
UPDATE statement to connection 1 for instance and the SELECT query to ...
connection 2! Of course this will cause a deadlock.
I saw microsoft comments
http://support.microsoft.com/default...b;EN-US;170548 , but I am
not really using JET, since all my database calls use the dbSQLPassThrough
option. I also tried to disable the ODBC connection pool but this is
useless. I believe it really is the Drivers fault since I can get these
statements to work in Sybase and Oracle.
This is the kind of stuff that puzzles me the most. The whole microsoft
architecture tries to be smarter than you are and takes control of
everything, but fails to do the simplest things AND it really seems you
cannot disable it.
Any help would be very much appreciated.
SerGioGio
Hi
What error are you getting?
Have you considered using ADO insterad of DAO?
DAO is a very old technology so I am trying to remember how the stuff worked
10 years ago.
Since you are doing a read, use dbForwardOnly (I think that is what is it)
for the rs.
rs.BeginTran and rs.CommitTran are required.
Regards
Mike
"SerGioGio" wrote:
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once, even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
>
|||Hello Mike,
Thanks for your quick answer.
I am only getting a "Timeout Expired" Error after 1 minute or so.
I tried your suggestion but with no luck
The whole app is written in DAO so I cannot switch to ADO unfortunately. In
addition I am not even sure ADO will fix that. We always need the most
advanced technology to support the most basic stuff. On the other hand,
client-side cursors, distributed transactions, access database links,
connection pool are available in DAO since early stages...
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> Hi
> What error are you getting?
> Have you considered using ADO insterad of DAO?
> DAO is a very old technology so I am trying to remember how the stuff
worked[vbcol=seagreen]
> 10 years ago.
> Since you are doing a read, use dbForwardOnly (I think that is what is it)
> for the rs.
> rs.BeginTran and rs.CommitTran are required.
> Regards
> Mike
> "SerGioGio" wrote:
very[vbcol=seagreen]
even[vbcol=seagreen]
not in[vbcol=seagreen]
at[vbcol=seagreen]
connections[vbcol=seagreen]
the[vbcol=seagreen]
...[vbcol=seagreen]
am[vbcol=seagreen]
dbSQLPassThrough[vbcol=seagreen]
|||Hi
Then you need to pass both statements at once (you may have to navigate
through the dataset collection to get to the 2nd executions output) or create
a stored procedure that has the update and select in it. Input parameter of
'mycounter' and output parameter of count.
Regards
Mike
"SerGioGio" wrote:
> Hello Mike,
> Thanks for your quick answer.
> I am only getting a "Timeout Expired" Error after 1 minute or so.
> I tried your suggestion but with no luck
> The whole app is written in DAO so I cannot switch to ADO unfortunately. In
> addition I am not even sure ADO will fix that. We always need the most
> advanced technology to support the most basic stuff. On the other hand,
> client-side cursors, distributed transactions, access database links,
> connection pool are available in DAO since early stages...
> SerGioGio
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
> news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> worked
> very
> even
> not in
> at
> connections
> the
> ...
> am
> dbSQLPassThrough
>
>
|||Well it is a bit sad to rewrite all the sql just because of one dumb driver
(or whatever it is that fails here).
Using stored proc means writing one version for Oracle, one for Sybase, one
for SQL Server...
But I guess I will have to.
Sometimes I admire MS for their tool/concepts, sometimes I just find they
purposely push us to bloated solutions.
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:76FCBB7F-C446-4938-8122-D5B536D06417@.microsoft.com...
> Hi
> Then you need to pass both statements at once (you may have to navigate
> through the dataset collection to get to the 2nd executions output) or
create
> a stored procedure that has the update and select in it. Input parameter
of[vbcol=seagreen]
> 'mycounter' and output parameter of count.
> Regards
> Mike
>
> "SerGioGio" wrote:
In[vbcol=seagreen]
it)[vbcol=seagreen]
a[vbcol=seagreen]
counter,[vbcol=seagreen]
once,[vbcol=seagreen]
but[vbcol=seagreen]
application[vbcol=seagreen]
sends[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
these[vbcol=seagreen]
microsoft[vbcol=seagreen]
you[vbcol=seagreen]
|||Hi SerGioGio,
Close all opened recordset before to begin a transaction.
Vctor Koch.
"SerGioGio" <sergiogio@.yahoo.fr> escribi en el mensaje
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
|||Hello,
OK I eventually found a solution to this issue, hopefully this may help some
people.
I now believe that I was wrong, the responsible for multiple connections is
not ODBC, it is Jet! I was under the assumption that since I was using
dbSQLPassThrough in all my queries, I was getting rid of Jet, but not
completely actually as it turned out.
To completely get rid of Jet one must use VB's ODBCDirect, it's just a
matter of adding the flag dbUseODBC in the Workspace object creation option.
With this option I no longer get outstanding connections, and I have a great
control over the connections.
Hope this will help people not to struggle for a whole week like I did.
SerGioGio
"SerGioGio" <sergiogio@.yahoo.fr> a crit dans le message de
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
I have a problem with DAO, Transactions and SQLServer. I want to do a very
simple thing (in VB)!
BeginTrans
Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
'mycounter'", dbSQLPassThrough)
Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
value = Rec(0)
CommitTrans
Note how simple this is. I just want to get a new value for a counter,
UPDATing first in order to make sure each value is returned only once, even
in concurrent environment. This is what you learn in school.
Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
SQLServer using DAO/ODBC.
In SQLServer using DAO/ODBC the code just hangs the entire application at
the SELECT line.
I believe the problem comes from the ODBC SQL Server driver
(2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
are opened (by the driver I believe) and I suspect that the driver sends the
UPDATE statement to connection 1 for instance and the SELECT query to ...
connection 2! Of course this will cause a deadlock.
I saw microsoft comments
http://support.microsoft.com/default...b;EN-US;170548 , but I am
not really using JET, since all my database calls use the dbSQLPassThrough
option. I also tried to disable the ODBC connection pool but this is
useless. I believe it really is the Drivers fault since I can get these
statements to work in Sybase and Oracle.
This is the kind of stuff that puzzles me the most. The whole microsoft
architecture tries to be smarter than you are and takes control of
everything, but fails to do the simplest things AND it really seems you
cannot disable it.
Any help would be very much appreciated.
SerGioGio
Hi
What error are you getting?
Have you considered using ADO insterad of DAO?
DAO is a very old technology so I am trying to remember how the stuff worked
10 years ago.
Since you are doing a read, use dbForwardOnly (I think that is what is it)
for the rs.
rs.BeginTran and rs.CommitTran are required.
Regards
Mike
"SerGioGio" wrote:
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once, even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
>
|||Hello Mike,
Thanks for your quick answer.
I am only getting a "Timeout Expired" Error after 1 minute or so.
I tried your suggestion but with no luck
The whole app is written in DAO so I cannot switch to ADO unfortunately. In
addition I am not even sure ADO will fix that. We always need the most
advanced technology to support the most basic stuff. On the other hand,
client-side cursors, distributed transactions, access database links,
connection pool are available in DAO since early stages...
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> Hi
> What error are you getting?
> Have you considered using ADO insterad of DAO?
> DAO is a very old technology so I am trying to remember how the stuff
worked[vbcol=seagreen]
> 10 years ago.
> Since you are doing a read, use dbForwardOnly (I think that is what is it)
> for the rs.
> rs.BeginTran and rs.CommitTran are required.
> Regards
> Mike
> "SerGioGio" wrote:
very[vbcol=seagreen]
even[vbcol=seagreen]
not in[vbcol=seagreen]
at[vbcol=seagreen]
connections[vbcol=seagreen]
the[vbcol=seagreen]
...[vbcol=seagreen]
am[vbcol=seagreen]
dbSQLPassThrough[vbcol=seagreen]
|||Hi
Then you need to pass both statements at once (you may have to navigate
through the dataset collection to get to the 2nd executions output) or create
a stored procedure that has the update and select in it. Input parameter of
'mycounter' and output parameter of count.
Regards
Mike
"SerGioGio" wrote:
> Hello Mike,
> Thanks for your quick answer.
> I am only getting a "Timeout Expired" Error after 1 minute or so.
> I tried your suggestion but with no luck
> The whole app is written in DAO so I cannot switch to ADO unfortunately. In
> addition I am not even sure ADO will fix that. We always need the most
> advanced technology to support the most basic stuff. On the other hand,
> client-side cursors, distributed transactions, access database links,
> connection pool are available in DAO since early stages...
> SerGioGio
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
> news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> worked
> very
> even
> not in
> at
> connections
> the
> ...
> am
> dbSQLPassThrough
>
>
|||Well it is a bit sad to rewrite all the sql just because of one dumb driver
(or whatever it is that fails here).
Using stored proc means writing one version for Oracle, one for Sybase, one
for SQL Server...
But I guess I will have to.
Sometimes I admire MS for their tool/concepts, sometimes I just find they
purposely push us to bloated solutions.
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:76FCBB7F-C446-4938-8122-D5B536D06417@.microsoft.com...
> Hi
> Then you need to pass both statements at once (you may have to navigate
> through the dataset collection to get to the 2nd executions output) or
create
> a stored procedure that has the update and select in it. Input parameter
of[vbcol=seagreen]
> 'mycounter' and output parameter of count.
> Regards
> Mike
>
> "SerGioGio" wrote:
In[vbcol=seagreen]
it)[vbcol=seagreen]
a[vbcol=seagreen]
counter,[vbcol=seagreen]
once,[vbcol=seagreen]
but[vbcol=seagreen]
application[vbcol=seagreen]
sends[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
these[vbcol=seagreen]
microsoft[vbcol=seagreen]
you[vbcol=seagreen]
|||Hi SerGioGio,
Close all opened recordset before to begin a transaction.
Vctor Koch.
"SerGioGio" <sergiogio@.yahoo.fr> escribi en el mensaje
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
|||Hello,
OK I eventually found a solution to this issue, hopefully this may help some
people.
I now believe that I was wrong, the responsible for multiple connections is
not ODBC, it is Jet! I was under the assumption that since I was using
dbSQLPassThrough in all my queries, I was getting rid of Jet, but not
completely actually as it turned out.
To completely get rid of Jet one must use VB's ODBCDirect, it's just a
matter of adding the flag dbUseODBC in the Workspace object creation option.
With this option I no longer get outstanding connections, and I have a great
control over the connections.
Hope this will help people not to struggle for a whole week like I did.
SerGioGio
"SerGioGio" <sergiogio@.yahoo.fr> a crit dans le message de
news:OyCDm4SpEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once,
even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not
in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends
the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
> I saw microsoft comments
> http://support.microsoft.com/default...b;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
> Any help would be very much appreciated.
> SerGioGio
>
DAO to ADO conversion
Hi Techies,
We had a database MS-Access with DAO statements and we are upgrading our Database to MS-SqlServer which in need to convert the DAO statements to ADO statements.
What i want to know is there any free tool which converts automatically to convert DAO statements to ADO statements. If so what is it?
If not what is the easiest procedure to convert or otherwise should I have to do it manually convert all those DAO statements(so many). If I have to do it manually can u explain where i need to take care mainly while converting the statements.
Thank UThe Access Upsizing Wizard (http://support.microsoft.com/default.aspx?scid=kb;en-us;325017) is probably what you want.
-PatP
We had a database MS-Access with DAO statements and we are upgrading our Database to MS-SqlServer which in need to convert the DAO statements to ADO statements.
What i want to know is there any free tool which converts automatically to convert DAO statements to ADO statements. If so what is it?
If not what is the easiest procedure to convert or otherwise should I have to do it manually convert all those DAO statements(so many). If I have to do it manually can u explain where i need to take care mainly while converting the statements.
Thank UThe Access Upsizing Wizard (http://support.microsoft.com/default.aspx?scid=kb;en-us;325017) is probably what you want.
-PatP
DAO Error code 0xbd7
Hi
I have a button in vb calling crsytal reports 9 . These reports are opening in cr viewer. I have an access database secured with a password. I can open the report but when I go to enter new parameters in the cr viewer, I get this error.
"LOGON FAILED"
Details: DAO Error Code 0xbd7
Source: Dao.Workspace
Description: Not a valid Password
Im lost on this one and I would be grateful for some help.
Thanks!!Hello,
This is in C#, I thing it can help you :
crystalReportDocument1.DataSourceConnections[0].SetConnection(SERVER, Database, "USERNAME", "YOUR PASSWORD"); crystalReportViewer1.ReportSource = crystalReportDocument1;
I use Microsoft Access file, so I left the Username blank, and for Server and Database put the new path to my .mdb file.
I hope it helps.
I have a button in vb calling crsytal reports 9 . These reports are opening in cr viewer. I have an access database secured with a password. I can open the report but when I go to enter new parameters in the cr viewer, I get this error.
"LOGON FAILED"
Details: DAO Error Code 0xbd7
Source: Dao.Workspace
Description: Not a valid Password
Im lost on this one and I would be grateful for some help.
Thanks!!Hello,
This is in C#, I thing it can help you :
crystalReportDocument1.DataSourceConnections[0].SetConnection(SERVER, Database, "USERNAME", "YOUR PASSWORD"); crystalReportViewer1.ReportSource = crystalReportDocument1;
I use Microsoft Access file, so I left the Username blank, and for Server and Database put the new path to my .mdb file.
I hope it helps.
DAO and SQL Server
I want to create a new database on a SQL Server using DAO.
Is it possible and if so, Does anyone have any example how to do so?
Thanks,Possible: Yes
Examples: No
Advice: Don't do it.|||he he
I don't have much choice. We are running DAO here in my office and then don't want to ship ADO.
How would I go about doing it?
How would I connect to the server?
I can then run the Create Database SQL command, correct?
Thanks,|||Oh yeah, I don't want to use ODBC.|||Is this MSDE and not SQL Server?
If it's SQL Server, don't you have SQL Server client tools?|||Yes it is MSDE.
I have to do it all programmatically.
The point for this application will be to take an existing Access database and convert it to MSDE and then transfer all the data.
I have to do it programmatically due to the following reasons:
over 600 clients
clients have different versions of the application
only DAO is distributed.|||Originally posted by vbgladiator
Yes it is MSDE.
I have to do it all programmatically.
The point for this application will be to take an existing Access database and convert it to MSDE and then transfer all the data.
I have to do it programmatically due to the following reasons:
over 600 clients
clients have different versions of the application
only DAO is distributed.
Let's all bow or heads in a moment of silence...|||he he he
thank you, thank you.
he heh e
I know, this is a pain.
I had it in ADO but I don't even know how to connect to SQL server using DAO.|||Check out the link in this thread...
it's a link to some shareware that you might find useful...
http://www.dbforums.com/t972789.html
(would help if I paste it here, wouldn't it)|||What thread?
Is it possible and if so, Does anyone have any example how to do so?
Thanks,Possible: Yes
Examples: No
Advice: Don't do it.|||he he
I don't have much choice. We are running DAO here in my office and then don't want to ship ADO.
How would I go about doing it?
How would I connect to the server?
I can then run the Create Database SQL command, correct?
Thanks,|||Oh yeah, I don't want to use ODBC.|||Is this MSDE and not SQL Server?
If it's SQL Server, don't you have SQL Server client tools?|||Yes it is MSDE.
I have to do it all programmatically.
The point for this application will be to take an existing Access database and convert it to MSDE and then transfer all the data.
I have to do it programmatically due to the following reasons:
over 600 clients
clients have different versions of the application
only DAO is distributed.|||Originally posted by vbgladiator
Yes it is MSDE.
I have to do it all programmatically.
The point for this application will be to take an existing Access database and convert it to MSDE and then transfer all the data.
I have to do it programmatically due to the following reasons:
over 600 clients
clients have different versions of the application
only DAO is distributed.
Let's all bow or heads in a moment of silence...|||he he he
thank you, thank you.
he heh e
I know, this is a pain.
I had it in ADO but I don't even know how to connect to SQL server using DAO.|||Check out the link in this thread...
it's a link to some shareware that you might find useful...
http://www.dbforums.com/t972789.html
(would help if I paste it here, wouldn't it)|||What thread?
DAO and SQL server
We are considering migrating a large piece of VB code from Access to
SQL server. All the code is in DAO, are there any known
incompatibilities WRT SQL server and DAO?
SQL server. All the code is in DAO, are there any known
incompatibilities WRT SQL server and DAO?
Many thanks
SiSd (sd_bradford@.hotmail.com) writes:
> We are considering migrating a large piece of VB code from Access to
> SQL server. All the code is in DAO, are there any known
> incompatibilities WRT SQL server and DAO?
I have never used DAO, but a colleague of mine told me the other day,
that DAO was designed for Access, and is cumbersome to use with SQL Server.
In any case, DAO is old technology, and I would suspect that you don't get
full support for newer features in SQL Server with DAO, but I could be wrong
on that particular point. Nevertheless, I would consider ripping out DAO
in favour of ADO which is more up to date. Admittedly, I find ADO quite
ugly as well, and ADO .Net is a lot more palatable. That would however call
for a migration to VB .Net, which may be an overkill.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Sd" <sd_bradford@.hotmail.com> wrote in message
news:e66eafb3.0412200220.17a18286@.posting.google.c om...
> We are considering migrating a large piece of VB code from Access to
> SQL server. All the code is in DAO, are there any known
> incompatibilities WRT SQL server and DAO?
> Many thanks
> Si
There's stuff you can do with tables and queries which would probably cause
a problem.
You can forget compact and repair operations.
Table search is potentially a problem as you'll minimum have to switch to
(SQL) attached tables.
Hopefully the code and data is already split between two databases?
If you just use a bunch of selects and inserts then they're quite possibly
going to be relatively painless to convert.
If you have to do it anyhow.
I would suggest create corresponding sql tables manually.
Perhaps start off using DTS to load em in and see what you get.
Attach these to a copy of your code database.
Give it a twirl and see what happens.
--
Regards,
Andy O'Neill
DAO > SQL Server Processes
I need to know exactly what VBA lines Create and Destroy SQL Server
Processes - i.e. those visible in Enterprise Manager under Management
> Current Activity > Process Info.
Processes - i.e. those visible in Enterprise Manager under Management
> Current Activity > Process Info.
Why?
I am experiencing strange behaviour with Processes that are created
when I create a DAO Database Object with the following line:
Set m_ResDatabase = DBEngine.Workspaces(0).OpenDatabase(strDSN, False,
False, strODBC)
This creates the process as expected.
However the following lines don't always close the ensuing Process:
If Not m_ResRecordSet Is Nothing Then
m_ResRecordSet.Close
Set m_ResRecordSet = Nothing
End If
If Not m_ResDatabase Is Nothing Then
m_ResDatabase.Close
Set m_ResDatabase = Nothing
End If
If Not m_ResWorkspace Is Nothing Then
m_ResWorkspace.Close
Set m_ResWorkspace = Nothing
End If
It seems as if SQL Server keeps hold of the first two Processes and
then will release any subsequent ones.
Can anyone shed any light in this - or any good web pages where I
might find some answers?
Regards ChrisChris (chris.laycock@.addept.co.uk) writes:
> I am experiencing strange behaviour with Processes that are created
> when I create a DAO Database Object with the following line:
> Set m_ResDatabase = DBEngine.Workspaces(0).OpenDatabase(strDSN, False,
> False, strODBC)
> This creates the process as expected.
> However the following lines don't always close the ensuing Process:
> If Not m_ResRecordSet Is Nothing Then
> m_ResRecordSet.Close
> Set m_ResRecordSet = Nothing
> End If
> If Not m_ResDatabase Is Nothing Then
> m_ResDatabase.Close
> Set m_ResDatabase = Nothing
> End If
> If Not m_ResWorkspace Is Nothing Then
> m_ResWorkspace.Close
> Set m_ResWorkspace = Nothing
> End If
> It seems as if SQL Server keeps hold of the first two Processes and
> then will release any subsequent ones.
> Can anyone shed any light in this - or any good web pages where I
> might find some answers?
Do DAO have connection pooling? Modern client libraries have connection
pooling, which means that when you close a connection from the code,
the API lingers on the connection for a minute, in case you would
reconnect directly. In such case, it's perfectly normal to see the
connections around.
Else, the only reason I can think of is that you had a transaction in
progress when you closed the connections, and the rollback takes a
long time. If you vie the processes with sp_who what state and active
command do they have?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Do DAO have connection pooling? Modern client libraries have connection
> pooling, which means that when you close a connection from the code,
> the API lingers on the connection for a minute, in case you would
> reconnect directly. In such case, it's perfectly normal to see the
> connections around.
> Else, the only reason I can think of is that you had a transaction in
> progress when you closed the connections, and the rollback takes a
> long time. If you vie the processes with sp_who what state and active
> command do they have?
I've seen this in Access and it's a PITA as it makes changing users
impossible without restarting it, e.g. log in as "sa" then close
everything and log in as "joe", the front end thinks you are "joe" but
the back end thinks you are "sa", which can cause unpredictable results.
If it's connection pooling in place then I don't think it was
implemented right. I've seen it work the other way as well while logged
in as normal user I then try to log in as "sa" to manage users, etc and
get told I have no permission to do it.
Subscribe to:
Posts (Atom)