1

INTRODUCTION (tldr; question down below)

First off, yes I know DAO:

  1. is obsolete
  2. no longer supported
  3. inefficient for SQL
  4. sucks in general

So answers along those lines aren't helpful to the problem.

I've long since shifted my preferences to NHibernate and other means of connecting to data. However, I'm on a legacy project where the first stage of the plan is to migrate the data from many Access databases to a single SQL Server instance. Easy enough. The data migration has been done and works fine.

However, the application is VB6 code using DAO to connect to Access and I need to change this as minimally as possible for the first stage. The next phase (soon) is to convert the whole application to an ASP.NET MVC site hitting SQL Server properly.

As I search around for how to properly structure the DAO OpenDatabase() function to hit SQL Server, all the answers are the aforementioned "Why do you want to do that?" "You should learn ADODB!" "DAO is for suckers!" nonsense that never addresses the actual question asked. Sadly the original question remains in the search engine even though it's marked "solved" on particular forums, etc...

SO... THE QUESTION

Given all of this, is there ANYONE who still remembers how to connect to SQL Server using DAO OpenDatabase?

Yes, this is comparable to drilling a hole in a piece of wood with a brace and bit? Sure a power drill is better, smarter, faster, and all that goodness... but sometimes the old ways are required.

Improve this question
13
  • 1
    I had to migrate recently a VB6 with DAO application from SQL7 to SQL 2016, and the process was almost straight forward. I didn't change anything in code, except old SQL Syntax that is obsolete now. So, what is your issue? Can you be more precise? Commented Nov 12, 2018 at 19:00
  • 1
    It is difficult to provide useful information when you don't ask a very specific question. Saying "how do I structure ..." implies a question that is far too broad for SO. But if your question is along the lines of "how do I connect to a sql server database using dao OpenDatabase method", then it seems the doc has such an example. Is that not useful? Commented Nov 12, 2018 at 19:02
  • 1
    I used ODBC. So, here how I open SQL connection with DAO : connect = "DSN=<odbcname>;UID=<username>;PWD=<password>;DATABASE=<database>" Set datab = rdoEnvironments(0).OpenConnection("", rdDriverCompleteRequired, False, connect) This is the solution for us, for having less changed to do in our old app. Note this is not the only way to do this. Commented Nov 12, 2018 at 19:10
  • 1
    Tell me before if this solve your issue. Commented Nov 12, 2018 at 19:13
  • 1
    Remember you have to use 32 bits ODBC driver. If you are on 64 bits system, take the good one (C:\Windows\SysWOW64\odbcad32.exe). And I think I have used SQL Server driver. SQL Native client are too recent for DAO (I think). You may have to try many. Commented Nov 12, 2018 at 19:17

2 Answers 2

Reset to default
3

I had to migrate recently a VB6 with DAO application from SQL7 to SQL 2016, and the process was almost straight forward. I didn't change anything in code, except old SQL Syntax that is obsolete now.

I used ODBC. So, here how I open SQL connection with DAO :

connect = "DSN=<odbcname>;UID=<username>;PWD=<password>;DATABASE=<database>"
Set datab = rdoEnvironments(0).OpenConnection("", rdDriverCompleteRequired, False, connect)

This is the solution for us, for having less changed to do in our old app. Note this is not the only way to do this.

Remember you have to use 32 bits ODBC driver. If you are on 64 bits system, take the good one (C:\Windows\SysWOW64\odbcad32.exe). And I think I have used SQL Server driver. SQL Native client are too recent for DAO (I think). You may have to try many.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you... I did have the 64 bit ODBC setup and not 32 bit. That correction and the proper structure of the connectionstring were the keys. Instead of RDO and OpenConnection, was still able to use ADO and OpenDatabase to minimize my code changes.
3

I think one way you could do that isuse an ODBC connection. On Administrative tools form control panel y can add an ODBC Source to you SQL Server. After doing that, you could try this:

Dim worksp As Workspace
Dim conexString As String
Dim base As Variant
Set worksp = DBEngine.Workspaces(0)
Let conexString = "OBDC;DSN=SQLDNSNAME;UID=;PWD="
Set base = worksp.OpenDatabase("BDDNAME", False, False, conexString)
Improve this answer

1 Comment

You're Welcome, happy to help u

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.