DAO in Access Data Project

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
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.
 
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
 
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.
 
Sylvain Lafontaine said:
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.
Click to expand...

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
 
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.
 
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
 
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)
 
Sylvain Lafontaine said:
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 » :
Click to expand...

dbSeeChanges, I know about. dbFailOnError, I did not know about, but
possibly it is the equivelant of On Error Resume Next?
 
Sylvain Lafontaine said:
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)
Click to expand...

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?
 
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.
 
Back
Top