Microsoft Jet Workspaces and SQL Server

[Roger Eriksen]

Hi

I am upsizing an old Access application.
The application contains some DAO-code that uses methods that is only for
Microsoft Jet Workspaces.
I am upsizing the database so that tables are attached to my application.
I have trouble using OpenRecordset method with dbOpenTable parameter.
dbOpenDynaset works fine.
The code uses the "seek" method on the recordset i lots of occations.

Any ideas?

Regards
Roger

 

[Arvin Meyer [MVP]]

Seek is much faster, but is a method of Jet. Jet can be run as a layer over
a SQL engine, but then the SQL engine totally loses any advantage it may
have had for speed, and using seek has no advantage at all. You can change
your seek code to use DLookup, or for large data sets, just build an indexed
recordset and use a join and/or a where clause to get the record.

I'd use Speed Ferret to find all instances of the word "seek" and replace
the code.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Roger Eriksen]

I need a quick solution and I would like to find a way to use the seek
method.
This works, but i cannot use glbPasienRS.seek:
Set glbDataDB = DBEngine.Workspaces(0).Databases(0)
Set glbPasientRS = glbDataDB.OpenRecordset(tablename, dbOpenDynaset,
dbSeeChanges)

This does not work, but if it did, I would be able to use glbPasienRS.seek:
Set glbDataDB = DBEngine.Workspaces(0).Databases(0)
Set glbPasientRS = glbDataDB.OpenRecordset("TableName", dbOpenTable)

"TableName" is an attached SQL Server table.

Roger

 

[Douglas J. Steele]

You indicated that your tables are linked, as opposed to being in the same
MDB as your code.

You cannot use dbOpenTable with linked tables. See
http://www.mvps.org/access/tables/tbl0006.htm at "The Access Web" for the
work-around.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Roger Eriksen]

Thanks - I found the code.

I am trying to run this, but I get an error when running.
CurrentDb().TableDefs(TableName).Connect returns a string:
?currentdb().TableDefs("PASIENT").Connect
ODBC;DRIVER=SQL Server;SERVER=ROGERDEV;APP=Microsoft Office
2003;WSID=ROGERDEV;DATABASE=InVitro3;Trusted_Connection=Yes

Is this right for this code? Why are you using the MID function?

Public Function OpenForSeek(TableName As String) As Recordset
' Assume MS-ACCESS table
Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase _
(Mid(CurrentDb().TableDefs(TableName).Connect, _
11), False, False, "").OpenRecordset(TableName, _
dbOpenTable)
End Function

 

[Douglas J. Steele]

You didn't mention that your back-end was SQL Server.

I don't believe you'll be able to use Seek.