Advice needed - MSDE vs. Access

Hi, I currently have a application that is being used throughout the
country at different customers. Currently it is a VB6 application accessing
DBF/CDX standalone tables via ODBC. We are developing the 'next
generation' of the program in VB.NET and will do data access via OLEDB.
Here is the issue: Stand alone Visual Foxpro DBF tables do not return
'primary key' information, so we lose a lot of potential functionality by
not being able to set relations in a Dataset. Therefore, we are looking
for a new database to host the application. The application DB requirements
are basic INSERT, UPDATE, DELETE. No data replication, or anything fancy.
No "DB security" required. In reality, the application is coded so that it
will actually run on a SQL server or Oracle enterprise DB, too. But
customers that choose that option have an IT dept with appropriate
expertise, so I'm not worried about them.
Here's the problem... most of our customers are in small shops and are
barely computer literate. We need something REALLY simple. With the DBF
files it couldn't be simpler... install the program and then run it. No
real maintenance (except backup of course) is needed. I'm looking at
either MS Access or MSDE as a replacement database. Given that my
customers are the types that use the CD drawer as a cup holder, I'm
concerned that MSDE might be too much for them, and that Access might be
easier for them. Right now this is just my initial thought, as I'm not
familiar with MSDE. Before I get too far down the road, I wanted to tap
some expertise for advise.
So, the application will be coded to the 'lowest common denominator' but
will able to run on SQL server and ORACLE also. The question is, given the
small shops I need to cater to, what should that 'lowest common denominator'
be? Access or MSDE?
Any advice graceously accepted... Thanks.
John
hi John,
JohnR wrote:
>...
> So, the application will be coded to the 'lowest common denominator'
> but will able to run on SQL server and ORACLE also. The question is,
> given the small shops I need to cater to, what should that 'lowest
> common denominator' be? Access or MSDE?
>
difficult question, as the 2 engines can not be compared... ok they are both
database engines, but very different.... from my point of view I'd go with
MSDE as it can be easily scaled to full blown SQL Server editions with no
harm at all... and as you already know, JET database engine is in
maintenance and no additional features will be provided for it..
on the other side, MSDE, and soon SQLExpress, could require some more
maintenace (you can include in you application, for quite all administrative
tasks)... but they are another level of db engines, comparable with Oracle,
where the JET engine is not...
for SQL Server connections I'd go for the SQLClient name space versus the
OLEDB conterpart asi it provides better and targeted support for the SQL
Server worls, where the OLEDB provider is quite generic... but this is
another story..
more... SQLExpress will support easy setup and deployment for your database,
as long as XCopy support...
http://msdn.microsoft.com/library/de...seoverview.asp
provides additional info..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph
|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx
|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ADODB.Recordset Invalid Column Name VB6

I'm trying to limit the recordset retuned in the sub below by a
variable called vcnumber. I'm not sure what the problem is but I keep
getting the error "Invalid Column Name" when I set sqlstring to "select
* from vc_names where vc_case_number =" & vcnumber. If I don't try to
limit the recordset returned and just set sqlstring to "select * from
vc_names", it works. If there is another\better way to do this, I am
open to suggestions. Thanks!
========================================
===================
Public Sub populatename()
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectString As String
Dim sqlstring As String
Set adoConnection = CreateObject("ADODB.Connection")
Set adoRecordset = CreateObject("ADODB.Recordset")
connectString = "Provider=SQLOLEDB.1;Password=password;Persist Security
Info=True;User ID=user;Initial Catalog=db;Data Source=server"
adoConnection.Open connectString
sqlstring = "select * from vc_names where vc_case_number =" & vcnumber
adoRecordset.Open sqlstring, adoConnection, adOpenDynamic,
adLockOptimistic
Do Until adoRecordset.EOF
List2.AddItem adoRecordset!first_name & " " & adoRecordset!middle_name
& " " & adoRecordset!last_name
adoRecordset.MoveNext
Loop
adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub
========================================
========================if vs_case_number is a char field, remember to add quotes i.e.
sqlstring = "select * from vc_names where vc_case_number = '" &
vcnumber & "'"

ADO/VB6: Creating a primary key

Hi all,
I've narrowed down my problem to a key issue. If I don't create a key, then
ADO is happy. If I do, then I know get a complaint. I've whipped up the
code fragment below to illustrate my problem. I'm using ADO v2.8, although
v2.7 has the same problem.
Dim dbtblSQL as New ADOX.Table
Dim dbkeySQL as New ADOX.Key
With dbtblSQL
.Name = "New Table"
.Columns.Append "Col1", adInteger, 0
.Columns.Append "Col2", adInteger, 0
.Columns.Append "Col3", adInteger, 0
With dbkeySQL
.Name = "MyKey"
.Type = adKeyPrimary
.Columns.Append dbtblSQL.Columns("Col1") 'Problem line 1
.Columns("Col1").RelatedColumn = "Col1"
End With
.Keys.Append dbkeySQL
End With
Call dbcatSQL.Tables.Append(dbtblSQL)
VB6 now complains on problem line 1 stating "Object already in collection.
Cannot append", but If I don't "append", then the item really isn't in the
collection.
The question boils down to how to create a primary key related to a column
in a table, where the column already exists. Thanks in advance.
Martin A. Weinberger
ButterflyVista
http://www.butterflyvista.com/
(when responding to me by email, remove the "X_" from the beginning)
Hi Again,
I also tried the single line method. The code below still produces a "The
parameter is incorrect."
objTable.Columns("PrimaryKey_Field").Properties("A utoIncrement").Value =
True
objTable.Keys.Append "PrimaryKey", adKeyPrimary, "PrimaryKey_Field"
Martin A Weinberger
ButterflyVista
Please remove the x_ to send me an email directly.
|||I found the problem thanks to some outside help. :-) The problem turned out
to be that when you use the AutoIncrement attribute, you must append the
table before you can create keys on it. This little MS ommission cost me a
few days, but at least I learnt something, so I thought that I pass it on.
Martin A Weinberger
ButterflyVista
Please remove the x_ to send me an email directly.

ADO/VB6 Glitch

I am connecting a data grid to a vb6 form using the following code (the connection is fine-it connects to a sql server db).

Set rs = New ADODB.Recordset
rs.Open TableName, conn, adOpenKeyset, adLockOptimistic
Set dgPartData.DataSource = rs
dgPartData.Caption = "Event Registration for " & sEventName

The table name is syntactically correct. What happens is if the first record in the database table is selected all is fine. However, if the user scrolls beyond the first record then my form gets all weird with phantom objects appearing through the text boxes, etc..

I have traced the issue to the 'Set dgPartData.DataSource = rs' line. Is there something I should do when connecting a grid to a db in code that I am not doing?

Thanks!What data types used in that table?
How about collation settings on SQL Server and on Windows on App./Web server?|||THe data types are mostly varchar, int and bigint. I am not sure what you mean by collation settings.

I have discovered that it seems to be connected to a bug in the refresh method of the adodc data object that is fired when using the adCmdTable setting and then trying to call the refresh method of the data object.

I have appeared to have worked around it but it is a little awkward. Let me know what you are thinking with the settings questions, please.

Thanks!!|||IF its something to do from VB side then I don't interfere and comment.
As far as SQL server is concerned if you do not find any information from SQL Error log then simply follow the workaround you have adopted.

In general COLLATION setup is used to specify code page and sort order for character data. Where it does deal with Windows collation & SQL Collations, more about this topic can be found from BOOKS ONLINE.

BTW< what is the service pack level on SQL Server & OS?|||I am not sure. How do I find that out? I am using SQL Server 2000 and Windows XP. I am getting the latest updates on the OS automatically. Where do I find the latest SQL Server SPs?

Thanks a ton! I'll look into collation!|||From SQL Server query analyzer run SELECT @.@.VERSION and let me know the result.

You can get information on SPs from MS SQL (http://www.microsoft.com/sql) website and download'em.|||Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)|||should download XP ServicePack 1 and SQL Server Service Pack 3a|||No its SQL 2000 with NO SP!

Refer to the above link and download SP3 and apply, its recommended to fix issues and get rid of hackers.|||aha... do read to SP3a readme file or fixlist before applying SP3a, otherwise SP3 is enough to apply.

ADO write not commiting to database

This seems about the best place for my query however it does cross over a bit ... dont shoot me.

SQL 2000 sp4 on server 2003

dev env VB6

users XP Sp2

Mdac 2.8

I have a legacy app that required some simple ammendment, insert a single row of data into a simple table (Not rocket Science) Sample : insert into maintcon_mach (maintcon_id, mach_id, date) values (123, 456, getdate())

PK on maintcon_id, mach_id so that single relationship exists between maintcon_id and mach_id in the table.

The procedure above is followed by a verification procedure that checks to see if table maintcon_mach has a mach_id 456 against maintcon_id 123, this procedure returns true. So far so good, however if you step through the procedure till disconnecting from the database and then reconnect and do only the verification again, the data is missing.

The following steps to resolve have resulted in no improvement.

1. Verify MDAC in entire project and on users computers all 2.8

2 Remove transactions (just in case) and still the same.

3 Replace SQL insert with ADO (As below) and still the same.. tried this with and without transactions.

4 Tried referecing older MDACs

5 Installed SQLredist on sample machines and still the same.

rs.Open "maintcon_mach", conMach, adOpenForwardOnly, adLockOptimistic
With rs
.AddNew
!maintcon_id = lngmaint
!mach_id = lngMachID
!Date = Format(Now(), "yyyy-mm-dd")
.Update
End With

Is the problem in SQL or MDAC?

Perhaps I've missed something (Obviously have) but have run out of places to look... Any idea's

In advance ...thanx

I have thought that perhaps there may be pending uncommitted transaction on the table, a server stop and restart was done and the results are still the same.?

Still confused...

|||Put the insert into a stored proc and OK?

ADO Recorset / Set object = Nothing Issue?

I am having an issue of update statements not being
commited (lost). I am using ado 2.7 with vb6.
Here's an example that I am concerned about:
adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
adoRS.AddNew
adoRS.field("field1").value = "abc"
adoRS.update
set adoRS = Nothing
Since I am seting the ADORS object to nothing, is it
possible it's not finished executing and rolls the
transaction back?
Jason Roozeethis is technically the wrong group for this post, but here goes...
You need to make sure your cursor type supports the updates.
If I were you, I would not use rs.Update to post changes back to server.
You should consider using Command objects.
if you want more specifics, post back.
Greg Jackson
PDX, Oregon|||Your life will be a lot easier and your code will run faster if you
use SQL to update/insert/delete data. You can execute it in ADO from
either a Connection or a Command object. You also eliminate that extra
and unnecessary round trip to the database to create the recordset.
cmd.Execute "INSERT INTO MyTable (Field1) " & _
"VALUES ('" & stringvariable & "')"
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 6 Feb 2004 14:31:42 -0800, "Jason Roozee" <jason@.camcoinc.net>
wrote:
>I am having an issue of update statements not being
>commited (lost). I am using ado 2.7 with vb6.
>Here's an example that I am concerned about:
>adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
>adoRS.AddNew
>adoRS.field("field1").value = "abc"
>adoRS.update
>set adoRS = Nothing
>Since I am seting the ADORS object to nothing, is it
>possible it's not finished executing and rolls the
>transaction back?
>Jason Roozee|||Don't use a recordset to affect data. Recordsets are for *retrieving* data.
http://www.aspfaq.com/2191
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:b4bb01c3ed00$fe683150$a101280a@.phx.gbl...
> I am having an issue of update statements not being
> commited (lost). I am using ado 2.7 with vb6.
> Here's an example that I am concerned about:
> adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
> adoRS.AddNew
> adoRS.field("field1").value = "abc"
> adoRS.update
> set adoRS = Nothing
> Since I am seting the ADORS object to nothing, is it
> possible it's not finished executing and rolls the
> transaction back?
> Jason Roozee

ADO Recorset / Set object = Nothing Issue?

I am having an issue of update statements not being
commited (lost). I am using ado 2.7 with vb6.
Here's an example that I am concerned about:
adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
adoRS.AddNew
adoRS.field("field1").value = "abc"
adoRS.update
set adoRS = Nothing
Since I am seting the ADORS object to nothing, is it
possible it's not finished executing and rolls the
transaction back?
Jason Roozeethis is technically the wrong group for this post, but here goes...
You need to make sure your cursor type supports the updates.
If I were you, I would not use rs.Update to post changes back to server.
You should consider using Command objects.
if you want more specifics, post back.
Greg Jackson
PDX, Oregon|||Your life will be a lot easier and your code will run faster if you
use SQL to update/insert/delete data. You can execute it in ADO from
either a Connection or a Command object. You also eliminate that extra
and unnecessary round trip to the database to create the recordset.
cmd.Execute "INSERT INTO MyTable (Field1) " & _
"VALUES ('" & stringvariable & "')"
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 6 Feb 2004 14:31:42 -0800, "Jason Roozee" <jason@.camcoinc.net>
wrote:

>I am having an issue of update statements not being
>commited (lost). I am using ado 2.7 with vb6.
>Here's an example that I am concerned about :
>adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
>adoRS.AddNew
>adoRS.field("field1").value = "abc"
>adoRS.update
>set adoRS = Nothing
>Since I am seting the ADORS object to nothing, is it
>possible it's not finished executing and rolls the
>transaction back?
>Jason Roozee|||Don't use a recordset to affect data. Recordsets are for *retrieving* data.
http://www.aspfaq.com/2191
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:b4bb01c3ed00$fe683150$a101280a@.phx.gbl...
> I am having an issue of update statements not being
> commited (lost). I am using ado 2.7 with vb6.
> Here's an example that I am concerned about :
> adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
> adoRS.AddNew
> adoRS.field("field1").value = "abc"
> adoRS.update
> set adoRS = Nothing
> Since I am seting the ADORS object to nothing, is it
> possible it's not finished executing and rolls the
> transaction back?
> Jason Roozee

ADO Recordset time out during query..PLEASE HELP

PLEASE HELP!
I have a large query that takes about a minute to run from the SQL Query
Analyzer. Now when I run the query from VB6 it craps out 30 seconds into it.
Is there a way around this without manipulating my query. I'm attaching the
query maybe you'll see something I can adjust i'm not a SQL guy so my query
may come off as crude but it works.... sort of!
"SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
(Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
NewDateString + "', 102)) AND (Master.ProjectNumber >10)
AND (Not (Master.ProjectNumber=5682))
AND (Not (Master.ProjectNumber=4520))
AND (Not (Master.ProjectNumber=5535))
AND (Not (Master.ProjectNumber=6042))
AND (Not (Master.ProjectNumber=5867))
AND (Master.VolumeID Is Null)
ORDER BY Master.recordnumber;"
Thanks
Rob
IT guy!
at very quick glance,
is there a nonclustered index on ProjectNumber column?
is there a clustered index on the recordnumber column?
you might need to check Index Tunning Wizard for suggestions of possible
indexes
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C9D32D06-A9E8-450D-B53B-F45860A8D7C3@.microsoft.com...
> PLEASE HELP!
> I have a large query that takes about a minute to run from the SQL Query
> Analyzer. Now when I run the query from VB6 it craps out 30 seconds into
it.
> Is there a way around this without manipulating my query. I'm attaching
the
> query maybe you'll see something I can adjust i'm not a SQL guy so my
query
> may come off as crude but it works.... sort of!
> "SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
> INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
> WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
> (Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
> AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
> NewDateString + "', 102)) AND (Master.ProjectNumber >10)
> AND (Not (Master.ProjectNumber=5682))
> AND (Not (Master.ProjectNumber=4520))
> AND (Not (Master.ProjectNumber=5535))
> AND (Not (Master.ProjectNumber=6042))
> AND (Not (Master.ProjectNumber=5867))
> AND (Master.VolumeID Is Null)
> ORDER BY Master.recordnumber;"
> Thanks
> --
> Rob
> IT guy!
|||In addition to tuning the sql statement and making sure that you have
appropriate indexes in place, have you tried increasing the .CommandTimeout
(within your VB code)?
Keith
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C9D32D06-A9E8-450D-B53B-F45860A8D7C3@.microsoft.com...
> PLEASE HELP!
> I have a large query that takes about a minute to run from the SQL Query
> Analyzer. Now when I run the query from VB6 it craps out 30 seconds into
it.
> Is there a way around this without manipulating my query. I'm attaching
the
> query maybe you'll see something I can adjust i'm not a SQL guy so my
query
> may come off as crude but it works.... sort of!
> "SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
> INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
> WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
> (Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
> AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
> NewDateString + "', 102)) AND (Master.ProjectNumber >10)
> AND (Not (Master.ProjectNumber=5682))
> AND (Not (Master.ProjectNumber=4520))
> AND (Not (Master.ProjectNumber=5535))
> AND (Not (Master.ProjectNumber=6042))
> AND (Not (Master.ProjectNumber=5867))
> AND (Master.VolumeID Is Null)
> ORDER BY Master.recordnumber;"
> Thanks
> --
> Rob
> IT guy!

ADO recordcount returns -1

Hi All
Upgraded Vb6 program from Access 2K to MSDE 2K RelA
Now all my checks for rs.recordcount return -1, except if I use Client side
cursor OR
if I use... rs.Open sql, cn, adOpenKeyset, adLockReadOnly
Why is this?
I don't want to use client side cursors and I need to save data so
(adLockReadOnly) is no good
I have found that that checking for rs.eof seems reliable as a substitute
for recordcount but would like to understand the reasoning
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> Upgraded Vb6 program from Access 2K to MSDE 2K RelA
> Now all my checks for rs.recordcount return -1, except if I use
> Client side cursor OR
> if I use... rs.Open sql, cn, adOpenKeyset, adLockReadOnly
> Why is this?
> I don't want to use client side cursors and I need to save data so
> (adLockReadOnly) is no good
> I have found that that checking for rs.eof seems reliable as a
> substitute for recordcount but would like to understand the reasoning
this is quiet normal using ADO recorset as the recordcount will be available
only when the entire rowset has been populated, ant this is true only when a
..movelast operation ins performed and all the data has been transferred..
please be warned that performing a rs.movelast + rs.movefirst can be a
timeconsuming operation for big results...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> please be warned that performing a rs.movelast + rs.movefirst can be a
> timeconsuming operation for big results...
In addition to that, if you DO need to know how many rows you have
(which OP did not in this case), I would first run a select
count(afield) " and the same from/where clause that you use in the
other query. Actually, in those cases you can sometimes eliminate some
of the INNER JOINS that you might have to use when getting the full
field list, and some of the WHERE clause, too -- which will give you
your rowcount faster.
I do this a lot when I want to display a progress bar.
In addition, when looping through a recordset, I ALWAYS do until
rs.eof...I never use the .rowcount property for anything but advising
the user. Not sure WHY I do this, but I've been doing it for years...
Matt
Matt

ADO connection

In VB6 I use more Forms where users can Add, Edit, Delete Records from more tables.

WHICH IS THE BEST METHOD

1. To open a connection when user start the application and I close the connection when user leave the application.

In Login form (Public cn As ADODB.Connection)

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

OR

2. To Open a connection in each Form and close the connection when Form is UnLoadetThe first case is not possible because

Set cn = New ADODB.Connection (this is in Form Login)
In other form it is necesary to set the connection again.|||The rule is that you only keep the connection open as long as needed - and no longer. What are your concerns ?|||How many clients will use this application ?|||5-8 users|||Will the security credentials be different for each user ? What is the purpose for the application and the multiple forms ? Are you thinking about opening 1 connection object that will be used for multiple recordset/command objects ?|||How I can open a connection when user start the application and kepp open until user leave the application ?

In module:
Public cn As ADODB.Connection

In first form when is load:
SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use "cn" in other forms is not possible (is not open)|||Are you destroying/closing the connection before the 2nd form is loaded ?|||I don`t close the connection, but I close (Unload) the first form.
The connection is closed ?|||Where are you declaring - Public cn As ADODB.Connection ?|||This declaration is made on a Module

Public cn As ADODB.Connection

In the Startup Form (where user input UserName and Password)

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use this connection on other forms I can't because is Closed.
I want to open the connection only one time(when application startup), and if it possible to use the connection in all form.|||Open the connection in a module or class when login in form successfull. Close it when application main form is closed.|||This Looks Like VB code If it is Do not forget to set the DBconnection ( and all all objects associated to it) = nothing

set cn = nothing

I personnally like to use 1 public DB connection instead of multiple connections per app.

Hope this helps.
LJ|||Hello Gurus out there!

I want to know what would be the best method in opening a connection to server:

1. a connection (declared globally) that is open once during Login and access thru all forms and be closed only when application is terminated
What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

2. a connection is opened only when needed but everytime i execute a query against the database i have to open also that connection and terminate when it is not used...What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

Secondly, how can we know the resources used by the users that are connected to my SQL SERVER in terms of memory usage and CPU?

Im using VB/FOxPro and I want to know the best practice in terms of opening a connection to the database coz Im expecting to have 20 or more users online simultanously and hook to my server as soon as we are finished with our system.

I hope you can light up our minds with these concerns.

Thanks,

Bernie|||Bernie - What driver are you using ?