code referring to sql BE in an adp

[mharness]

Hello,

I've been working with recordsets in access on an access backend and getting
by ok but I am now trying to work with some ms-sql tables on a remote server
that are linked to an access adp and am having a problem.

I can run the following code on access/access but when I try to run in on
access/ms-sql it errors out with "object variable or with block variable not
set". I assume that this is occurring because even after I set dbs =
currentdb, dbs still equals nothing but I don't know how to set it to
anything except the current database.

Thanks for any help,

Mike

Sub Test()

Dim dbs As Database
Dim qry As String
Dim rst As Recordset

Set dbs = CurrentDb
qry = "select fname from tblaccounts"
Set rst = dbs.OpenRecordset(qry, dbOpenDynaset)
While Not rst.EOF
Debug.Print rst!fname
rst.MoveNext
Wend

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub

 

[Sylvain Lafontaine]

ADP are mainly based on ADO and not DAO; so there is not CurrentDB by
default. However, if you insist, you can create a DAO.Database object and
connect it to your backend database. Here are some information and pieces
of code (not from me) that have already been published in the past in the
m.p.access.adp.sqlserver newsgroup:

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


There is a blank space for "Data Source", "Initial Catalog" and "User ID".
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


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)

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.

Finally, using DAO inside an ADP project should only be considered as a
quick patch.

 

[mharness]

Hello Sylvain,

Thank you for your reply but yikes--I'm afraid that the solution is a lot
more involved (for me) than this problem deserves.

I think I should describe what I'm trying to do and perhaps you or someone
else can suggest another solution.

My client is currently using ms word once a week to merge the contents of an
access table into a document that they distribute to about 400 recipients .
They have now moved the backend to ms sql and because of the way the data
has been restructured they need to point to a query instead of one table.
In order to provide them this query, I had hoped to create a simple access
frontend from which they could choose their criteria and then I would create
the query on the fly and overwrite the old query each time they requested
new criteria. Using the same query name simplifies their merge procedure.

I tried using a parameter query but the request for parameters errors out
the merge.

Any other suggestions would be appreciated.

Best regards,

Mike

 

[Sylvain Lafontaine]

Don't expect ADP and ADO to work in the same way than a MDB file with ODBC
linked tables and DAO.

My suggestion: continue to use a MDB file with ODBC linked tables and SQL
passthrough queries. You can also create and use ADO objects in your VBA
code in the MDB file. Take a look at the m.p.access.externaldata newsgroup
for more info about ODBC linked tables and SQL passthrough queries.

 

[mharness]

Hello Sylvain,

I should have thought to try odbc myself but it's been a long time since I
used it and I had all but forgotten about it until you mentioned it. Thank
you for that and for the reference to the newsgroup.

Best regards,

Mike

 

[Sylvain Lafontaine]

I'm sorry, it was the microsoft.public.access.odbcclientsvr newsgroup that I
wanted to give as a reference.