DAO in Access Data Project

[Mark A. Sam]

Hello,

Does DAO not work in Access Data projects?

God Bless,

Mark A. Sam

 

[Sylvain Lafontaine]

Yes, they work perfectly. Here a piece of code that I've taken from the
web many years ago; so pardon me if I cannot give you the full reference:

Public Function DAODatabase() As DAO.Database

Dim cnn As ADODB.Connection
Dim dbDAO As DAO.Database
Dim strConnect As String

Set cnn = CurrentProject.Connection

' Is the connection based on MSdataShape- or SQLOLEDB-provider?

If InStr(cnn.Provider, "Microsoft.Access.OLEDB") > 0 Or
InStr(cnn.Provider, "MSDataShape") > 0 Or InStr(cnn.Provider, "SQLOLEDB") >
0 Then

' Build connection string
strConnect = "ODBC;driver=SQL Server;server=" & cnn.Properties("Data
Source") & ";"
' Database Name
strConnect = strConnect & "database=" & cnn.Properties("Initial
Catalog") & ";"
' SQL Server- or Windows-security?
If cnn.Properties("Integrated Security") = "SSPI" Then
strConnect = strConnect & "Trusted_Connection=Yes;"
Else
strConnect = strConnect & "UID=" & cnn.Properties("User ID") &
";"
strConnect = strConnect & "PWD=" & cnn.Properties("Password") &
";"
End If
Else
MsgBox "DAO-Database not opened!"
Set DAODatabase = Nothing
Exit Function
End If

' Open Database
Set dbDAO = DBEngine.OpenDatabase("", False, False, strConnect)
Set DAODatabase = dbDAO

End Function

Sub DAO_Test()
Dim db As DAO.Database
Dim rec As DAO.Recordset

Set db = DAODatabase()
Set rec = db.OpenRecordset("select * from tblFilme", dbOpenForwardOnly)
Do Until rec.EOF
Debug.Print rec!Filmtitel
rec.MoveNext
Loop
Set db = Nothing
End Sub

If you want to have transactions, you can also first open a Workspace.
Also, after taking a quick look at it, maybe it will be a good idea to
explicitely close the database before setting to Nothing.

 

[Mark A. Sam]

Slyvaine,

I tired this and got an error in this line:

strConnect = "ODBC;driver=SQL Server;server=" & cnn.Properties("DataSource")
& ";"

The error message was:

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name
or ordinal.

I guess it may need to know the server location and name? I don't know the
syntax for this. My SQL DB is on a remote server with and ip address and
server name that they supplied to me. Do you know how to enter it into the
connection string?

God Bless,

Mark

 

[Sylvain Lafontaine]

The major part of this code is only used to automatically build the ODBC
connection string and will work 90% of the time. This code also make the
assumption that the ADP project is already connected to the backend
SQL-Server.

However, in case of trouble, it's easy to build it yourself:

http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

You can also create a DSN or an Alias.

The only thing that change from standard connection string is that you have
to put « ODBC; » at its beginning.

 

[Mark A. Sam]

The major part of this code is only used to automatically build the ODBC
connection string and will work 90% of the time. This code also make the
assumption that the ADP project is already connected to the backend
SQL-Server.

That was my assumption also, until I received the error... I'll check out
the links you provided. I hope I can get this working. If so I may revamp
a client app into a project. Right now it is working with linked SQL Server
tables has unusual problems.

God Bless,

Mark

 

[Mark A. Sam]

I figured out the problem for anyone interested:

DataSource should be two words in this line:

strConnect = "ODBC;driver=SQL Server;server=" & cnn.Properties("DataSource")
& ";"
strConnect = "ODBC;driver=SQL Server;server=" & cnn.Properties("Data
Source") & ";"

As well, InitialCatalogue whould be two words:

strConnect = strConnect & "database=" & cnn.Properties("InitialCatalog") &
";"
strConnect = strConnect & "database=" & cnn.Properties("Initial Catalog") &
";"


Now its working great. Thank you Sylvain for your help. You are saving me
a lot of time and frustration.

 

[Sylvain Lafontaine]

For Update and other queries that modify the database, you also need to add
the parameter « dbSeeChanges » and for reasons that I don't remember, I also
add the parameter « dbFailOnError » :

Set db = DAODatabase()

Dim sql as string
sql = "Update Joueurs Set Joueurs.NoChandail = ...."

db.Execute sql, dbFailOnError Or dbSeeChanges

 

[Sylvain Lafontaine]

Also, don't forget that you need to specify to ask for DAO objects in the
VBA code:

Dim db As DAO.Database
Set db = DAODatabase()

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset (sql_string, dbOpenDynaset, dbSeeChanges)

 

[Mark A. Sam]

Also, don't forget that you need to specify to ask for DAO objects in the
VBA code:

Dim db As DAO.Database
Set db = DAODatabase()

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset (sql_string, dbOpenDynaset, dbSeeChanges)

Good point and this brings up another question. By using the DAO.,<Object>
and the ADO.<Object> references, does this exempt (prevent) the need to
reference the associated Libraries on the Tools...References menu?

 

[Sylvain Lafontaine]

No, this doesn't exempt it. The References are used with early binding and
things such as « Dim DB As DAO.Database
» which are, in fact, early binding.

To be exempt of references, you must use late binding and CreateObject() :

Dim DbEng as object
Set DbEng = CreateObject ("DAO.DBEngine")

Dim Db as object
Set Db = DbEng.workspaces(0).opendatabase ("c:\MSOffice\Access\" & _
"Samples\Northwind.mdb")

Make a search on Google with DAO and CreateObject to have more information
on this subject.