db.createSnapshot with sql server

[Andreas Wöckl]

Hi group!

We are currently trying to port an access 97 application to access 2002 with
sql server (ADP-Project). I am now qustioning myself whether there is a
possibility to access an sql server with the dao statement:

Dim db As Database
Dim sql
Dim snap As snapshot
Set db = CurrentDb()
sql = "select * from anything"
Set snap = db.CreateSnapshot(sql)

Anyone an idea whether I could use pieces of code like that in an adp
without porting?

best regards

andreas wöckl

 

[Andreas Wöckl]

Hi Breandan!

Can I make a woraround or do I have to port all the code to ADO Recordsets?

cu

andy

 

[Brendan Reynolds]

Well, the 'workaround' would be to continue using the MDB. The reason that
CurrentDb returns Nothing in an ADP is because CurrentDb returns a reference
to the current Jet database. Because an ADP is not a Jet database, there is
nothing to which CurrentDb can return a reference.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Sylvain Lafontaine]

Here a quick workaround: create a DAO.Database. Here a piece of code that I
have taken from the web many years ago; so pardon me if I can 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.

S. L.

 

[Brendan Reynolds]

Interesting, Sylvain, but, given that we already know the data source is SQL
Server, I'm curious as to why someone would do something like this from an
ADP? If someone is going to continue to use DAO, Jet and ODBC, then would it
not be more efficient to continue using the existing MDB? Isn't the
bypassing of Jet, and direct connection to SQL Server, the primary reason
for converting to an ADP in the first place?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Sylvain Lafontaine]

You are totally right, a full conversion is probably the best thing in terms
of simplicity, performance (?) and long term maintenance.

However, this little trick can save a lot of time in the early stages of a
conversion project to quickly get a full working solution, even if it's not
the last word. Also, even in the later stages, many of the multiple
database operations inside a program can often be accommodated by a less
than perfect solution; thus saving billing hours and money ($$) for the
client.

Few - if any - of my clients are of the Bill Gates type, being able to throw
out their money at the window for any conceivable reason.

S. L.

 

[Brendan Reynolds]

Indubitably. But then, continuing to use the existing MDB application with
ODBC-linked tables might save the client more. I think, before I could offer
any advise as to the best course of action, I'd need to know what it was
that Andreas and/or his client were hoping to gain from the process.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[aaron kempf]

use ADP.

MDB against ODBC is obsolete. Anyone that tells you otherwise, find out who
their boss is at Microsoft and get them fired.

-Aaron