ODBC and Data Control

[Steve Thomas]

I want to connect to a SQL Server using ODBC. (I have been successful using ADO and ODBC to connect but not for the following procedure) I am currently waiting for VB6 but my employer tells me they will not get it through purchasing for about a month.

Here is the problem:

- I have a DAO data control on my form.(I understand I can not use the ADO control until I upgrade to VB6)

- I set the Data Control Connect = ODBC;DSN=TESTSQL;UID=SA
- I set the Data Control DefaultCursorType = 1 - ODBC Cursor
- I Set the Data Control DefaultType = 1 - UseODBC

And then I try and Set the Data Control RecordSource but It will not let me do it through a drop down list. I did some reading and I believe I must use a SQL statement for RecordSource. I use the following: Select * from tTable.

It gives me an error.

'**************************
I can connect if I set the DefaultType to 2 - UseJet but then when I click on my data control to move forward or Back it locks up and freezes.

If anyone has had this same problem, I would love to here your solution.

Thanks

[Clunietp]

Hi Steve

You can use ADO right now (but not the data control)

data controls are problematic, and you'll usually want to use the ADO objects directly. Since you'll be converting to ADO in the near future, you might as well start now...

Just download the latest MDAC from http://www.microsoft.com/data and add a reference to Microsoft ActiveX data objects 2.x library

here is a tutorial on how to use ADO:
http://msdn.microsoft.com/library/ps...k/mdat3n3g.htm

HTH

Tom

[Steve Thomas]

Hey I just got a big suprise. I just got VB6 loaded about 1/2 hour ago. Therefore, no more questions about VB5. I created a form and placed an ADO data object on the form. I placed a text box on the form. Everything seems to be working except that it is very slow!!!!!!!. I have A sql SERVER (7.0) it takes about 1/2 second to move from one record to the next. Is this normal???

[JHausmann]

You might want to try the OLEDB driver for SQL Server and check to see if the MDAC installed with your version of VB is the latest.

[Steve Thomas]

I am running verison MDAC 2.5 release (2.50.4403.12). I hope I am wrong in my assesment but it looks like this is the wrong version. From what I have read, this is the Internet version. I must have installed this when I was first playing with MDAC and ADO with VB5.

Any suggestions?????

[Clunietp]

What does your connection string look like? Are you using a client or server side cursor?

[Steve Thomas]

CommandType = adCMDTable
CursorLocation = adUseServer (If I use Clinet it Freezes)
CursorType = OpenDynamic
RecordSource = vMaster (This is a view in SQL)
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TPO;Data Source=slc-custsql

I can use the following code and it is really fast:

Dim RC as Recordset
Set rc = New Recordset
rc.Open "Select * from Table", "DSN = TestSQL"


One other side note:
I can not use the RC. AbsolutePosition in the code above. Serge told me to add the RC.cursorLocation = adUseClient. However when I do, it is soooo slow. If I use adServer it is still very slow but not as slow as adUseClient. It does, however, give me the absolutePosition value.

[Clunietp]

That's weird!... Have you tried navigating thru a recordset (client side cursor) using the ADO objects? (no data control)

[Steve Thomas]

What If I don't have a good index, or if my primary key is not good??

I am new to SQL server and it may be I haven't built the table properly// Any suggestions there?

[Clunietp]

I doubt it in this case -- if that were true, then ODBC and OLEDB would both be slow....