Connecting VB6 textbox to DAO data Control to SQL Server

[Weegee_71]

Hi there

We have a VB6 application in which we want to connect a DAO data control to SQL Server.

Now I have placed a DAO Data control on my form and

Then on the Form_Load i wrote the following code:

Dim sConnectionString As String
Dim strSQLStmt As String

'-- Build the connection string
sConnectionString = "PROVIDER = MSDASQL;driver={SQL Server};database=AlliSQL;server=ChrisDev\SQLEXPRESS,1433;uid=;pwd=;"

strSQLStmt = "SELECT * FROM TransactionHeader WHERE TransactionHeader.TransactionType = 'POR'"

Data1.Connect = sConnectionString

Data1.RecordSource = strSQLStmt
Text1.DataSource = Data1

Text1.DataField = "TransactionGroup"

However i got the folllowing error at this line:

Text1.DataSource = Data1

Error Message : Method or data Member not found

How can i connect a DAO Data control to SQL Server and display data in Text1

[DataMiser]

Don't use the DAO control. Use ADO.

[SamOscarBrown]

I am assuming your query could return several rows; and as you said "*" in it, I assume you will be returning more than one field.
What precisely do you want to be shown in your textbox?

(And why are you using an old DAO data control instead of the more 'modern' ADODC?)
(But then, why use a control at all? Just query your database and return whatever you want directly to the textbox.)

[SamOscarBrown]

oops, Datamiser beat me to the first part, but again, unless you need it for some other purpose, don't use a 'control' at all.

[Weegee_71]

I am assuming your query could return several rows; and as you said "*" in it, I assume you will be returning more than one field.
What precisely do you want to be shown in your textbox?

(And why are you using an old DAO data control instead of the more 'modern' ADODC?)
(But then, why use a control at all? Just query your database and return whatever you want directly to the textbox.)

Correct I will be returning a number of fields...just testing this with 1 field at the moment.
Ok i should have been cleared in my opening post.

We have a set of VB6 applications which are connecting to an Access database via DAO data controls.
We are upsizing the Access database to SQL Server but we as there is code behind the DAO data controls, we
dont want to have recreate this using ADO Data control.

So idealy we just want to change the connection from the existing DAO data control to point to the SQL Server database

[techgnome]

Correct I will be returning a number of fields...just testing this with 1 field at the moment.
Ok i should have been cleared in my opening post.

We have a set of VB6 applications which are connecting to an Access database via DAO data controls.
We are upsizing the Access database to SQL Server but we as there is code behind the DAO data controls, we
dont want to have recreate this using ADO Data control.

So idealy we just want to change the connection from the existing DAO data control to point to the SQL Server database

If that's the route you want to take, then why not use Link Tables in Access? Link your tables to SQL Server. If you name everything the same, then your front end should just use the new linked tables as they are with no changes. Access acts as as a middle-man pass through for the data.

It's not a long-term solution, but it might keep things limping along until you can build a proper front-end using something else.

-tg

[Weegee_71]

Hi all

I have managed to connect the DAO data control to SQL server using the following:

Dim sConnectionString As String
Dim cn As DAO.Database
Dim strSQLStmt As String



'-- Build the connection string
sConnectionString = "ODBC;DRIVER=SQL Server;SERVER=ChrisDemo\SQLEXPRESS,1433;DATABASE=ChrisTest;Trusted_Connection=True;"


strSQLStmt = "SELECT * FROM TransactionHeader WHERE TransactionHeader.TransactionType = 'POR'"

Data1.Connect = sConnectionString



Data1.RecordSource = strSQLStmt
Data1.Refresh
Data1.Move First


'txtTransactionGroup.DataField = "TransactionGroup"
' txtTransactionGroup.DataField = "TransactionGroup"
' txtTransactionGroup.Refresh

Text1.DataField = "TransactionType"
Text1.Refresh

[SamOscarBrown]

So, are you saying you see in text1 the data you want to see??????

[VB6 Programming]

If that's the route you want to take, then why not use Link Tables in Access? Link your tables to SQL Server. If you name everything the same, then your front end should just use the new linked tables as they are with no changes. Access acts as as a middle-man pass through for the data.

It's not a long-term solution, but it might keep things limping along until you can build a proper front-end using something else.

-tg

Yes, that is the best approach. With a few minutes work you can connect to a SQL Server database without needing to modify your VB6 program.

As you are migrating your existing data from Access to SQL Server the tables and column (field) names should be the same on SQL Server as they were in Access. Simply set up an Access database (.mdb format) with linked tables (using the Linked Table Manager in Access) connecting to the SQL Server database, using an ODBC connection.

Then your existing VB6 program will be able to work with the linked table Access database (just as it always did) and actually use the data on the SQL database.

Your linked table Access database should be located on each PC drive and point to the SQL Server database on the server. You need the ODBC driver on each PC as well.

(If you were using a multi-user Access database you should have been using a linked table Access database on each PC pointing to the Access database on the server - but often Microsoft's recommendation to do this was ignored).

And this can be a permanent solution, not just a temporary fix.