Accessing SQL data from an ACCESS app...

I have just imported into SQL server all of my Access 2000 tables. In my
front end Access app, I now am using linked tables to access the data in the
SQL server db.
The speed isn't the greatest.
Is there a better connection type I can use in Access - other than ODBC that
provides better speed?
Thanks,
Brad
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I have just imported into SQL server all of my Access 2000 tables. In my
> front end Access app, I now am using linked tables to access the data in
the
> SQL server db.
> The speed isn't the greatest.
> Is there a better connection type I can use in Access - other than ODBC
that
> provides better speed?
> Thanks,
> Brad
The speed problem isn't ODBC - it's your code. You've only just begun the
journey of porting an Access app to SQL Server. For instance, you open a
recordset in code on two large tables using a JOIN. You only want to add one
record. But Access will fetch all records from both tables across the
network (locking them in the process), do the JOIN, move to the end, and
insert the field, then send it all back. If you turn on ODBC tracing and
excute a query, you'll see what I'm talking about (this will make it
*really* crawl - don't do it for long!). You need to do a lot of optimizing
and change many of your queries to SQL Pass-Throgh. I recommend you get a
good book - my favorite is Microsoft Access Developer's Guide to SQL Server
by Chipman and Baron, SAMS Publishing. Good luck!
|||What is the difference /benefits of a pass-through query and any other SQL
query? I have never used a pass through query before..
Thanks,
Brad
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:O7c0b$DuEHA.2788@.TK2MSFTNGP09.phx.gbl...
> the
> that
> The speed problem isn't ODBC - it's your code. You've only just begun the
> journey of porting an Access app to SQL Server. For instance, you open a
> recordset in code on two large tables using a JOIN. You only want to add
one
> record. But Access will fetch all records from both tables across the
> network (locking them in the process), do the JOIN, move to the end, and
> insert the field, then send it all back. If you turn on ODBC tracing and
> excute a query, you'll see what I'm talking about (this will make it
> *really* crawl - don't do it for long!). You need to do a lot of
optimizing
> and change many of your queries to SQL Pass-Throgh. I recommend you get a
> good book - my favorite is Microsoft Access Developer's Guide to SQL
Server
> by Chipman and Baron, SAMS Publishing. Good luck!
>
|||A pass-through query passes the SQL statement to the server so that the
entire statement gets processed there, rather than returning lots of raw
data so that Access can process the query at the client computer. I'm
pretty sure you can find more information in the Access or SQL Server help.
I recently had surprisingly good results after upsizing an Access 2000
database to SQL Server, using only ODBC linked tables to SQL server 2000.
My users can now use the application at an acceptable speed from remote
high-speed VPN connected locations. Access Front-End/Back-end could never
have done that.
One thing I've done a lot of in Access is to write VBA functions to use in
Query criteria so I could fill in parameters in code without using SendKeys
to fill in parameter prompts. I use this method for Forms and Reports, and
I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
driver broke-down my queries so they were sent to SQL server with literal
parameters, and I got back only the records I was looking for. You can use
SQL Server Profiler to see the SQL statements that get sent to your server
from Access - that can give you a lot of insight into what's going on in you
app.
I'm pretty sure filters still get applied locally, so you don't want to rely
on those to be your initial data filters on large recordsets.
Another way to bring improved performance would be to use and Access data
project file (.ADP), which provides a more true Client/Server application.
I thought I was going to have to go that route so that my application would
work for remote high-speed VPN users, but my app worked so well with ODBC
linked tables that I didn't have to go to the work of largely re-doing my
application.
Another book set I would recommend is "Access Developer's Handbook Set" by
Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
> one
> optimizing
> Server
>
|||You really should only be calling stored procedures from pass-through
queries. In addition, you shouldn't have linked tables at all if you want a
really scalable enterprise application. Use the VBA recordset and
passthrough queries calling stored procedures. Let the database server
perform the database work most efficiently using stored procedures.
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> What is the difference /benefits of a pass-through query and any other SQL
> query? I have never used a pass through query before..
> Thanks,
> Brad
> "Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
> news:%23nRIrqFuEHA.3088@.tk2msftngp13.phx.gbl...
my[vbcol=seagreen]
in[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
> one
> optimizing
a
> Server
>
|||So, you are saying do not use linked tables at all. Could you give me a
snippet of Access code that opens an SQL Server Db and calls a stored
procedure to do something simple such as select * from a table?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a[vbcol=seagreen]
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get
> a
>
|||That's great information...
When you are referring to writing an Access query that uses a VBA function
for it's criteria to fill in the parameters (I've never even used SendKeys
to fill in parameter prompts before), are you referring to replacing things
like "[Enter Customer Name]" as a row criteria with a function such as
GetCustName()? where function GetCustName() would display a screen where
the user enters the customers name they are looking for and then you set
GetCustName = txtCustName?
Also what is the ADP you referred to? Is this a reference you add in to the
project? Never even heard of that one!!
Thanks,
Brad
That sounds interesting and is likely something I'd want to do...
"Dynamo" <noone@.nowhere.com> wrote in message
news:uPsFHJPuEHA.3152@.TK2MSFTNGP14.phx.gbl...
> A pass-through query passes the SQL statement to the server so that the
> entire statement gets processed there, rather than returning lots of raw
> data so that Access can process the query at the client computer. I'm
> pretty sure you can find more information in the Access or SQL Server
help.
> I recently had surprisingly good results after upsizing an Access 2000
> database to SQL Server, using only ODBC linked tables to SQL server 2000.
> My users can now use the application at an acceptable speed from remote
> high-speed VPN connected locations. Access Front-End/Back-end could never
> have done that.
> One thing I've done a lot of in Access is to write VBA functions to use in
> Query criteria so I could fill in parameters in code without using
SendKeys
> to fill in parameter prompts. I use this method for Forms and Reports,
and
> I was VERY pleasantly surprised that Access and/or the SQL Server ODBC
> driver broke-down my queries so they were sent to SQL server with literal
> parameters, and I got back only the records I was looking for. You can
use
> SQL Server Profiler to see the SQL statements that get sent to your server
> from Access - that can give you a lot of insight into what's going on in
you
> app.
> I'm pretty sure filters still get applied locally, so you don't want to
rely
> on those to be your initial data filters on large recordsets.
> Another way to bring improved performance would be to use and Access data
> project file (.ADP), which provides a more true Client/Server application.
> I thought I was going to have to go that route so that my application
would[vbcol=seagreen]
> work for remote high-speed VPN users, but my app worked so well with ODBC
> linked tables that I didn't have to go to the work of largely re-doing my
> application.
> Another book set I would recommend is "Access Developer's Handbook Set" by
> Paul Litwin, Ken Getz, and Mike Gilbert from SYBEX publishing.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:%23HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
ODBC[vbcol=seagreen]
the[vbcol=seagreen]
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
a
>
|||Can you also give me a simple example (and code) of a passthrough SQL query
calling a stored procedure at the SQL Database level?
Thanks,
Brad
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> You really should only be calling stored procedures from pass-through
> queries. In addition, you shouldn't have linked tables at all if you want
a[vbcol=seagreen]
> really scalable enterprise application. Use the VBA recordset and
> passthrough queries calling stored procedures. Let the database server
> perform the database work most efficiently using stored procedures.
> "Brad Pears" <donotreply@.notreal.com> wrote in message
> news:#HHEn3GuEHA.2192@.TK2MSFTNGP14.phx.gbl...
SQL[vbcol=seagreen]
In[vbcol=seagreen]
> my
data[vbcol=seagreen]
> in
> ODBC
> the
a[vbcol=seagreen]
add[vbcol=seagreen]
and[vbcol=seagreen]
and[vbcol=seagreen]
get
> a
>
|||"Brad Pears" <donotreply@.notreal.com> wrote in message
news:eHmgt0RvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Can you also give me a simple example (and code) of a passthrough SQL
query
> calling a stored procedure at the SQL Database level?
> Thanks,
> Brad
I have a sub called SQLExecute that I use for Pass-Throughs. Here it is:
Public Sub SQLExecute(SQL As String, Optional rs As Variant)
'This function creates a SQL Pass Through query that optionaly returns
records
On Error GoTo Error_SQLExecute
Dim qdf As QueryDef
Dim errAny As error
Set qdf = DBEngine(0)(0).CreateQueryDef("")
qdf.Connect = gstrODBC
qdf.ODBCTimeout = 0
qdf.SQL = SQL
If IsMissing(rs) Then
qdf.ReturnsRecords = False
qdf.Execute
Else
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
End If
Exit_SQLExecute:
Set qdf = Nothing
Exit Sub
Error_SQLExecute:
For Each errAny In DBEngine.Errors
msgbox "Error " & errAny.Number & " from " & errAny.source & " = " &
errAny.Description, vbCritical, "Error " & errAny.Number & " - SQLExecute"
Next
Resume Exit_SQLExecute
End Sub
In this sub, what makes it a Pass-Trhough is setting the QueryDef's Connect
property. In my case, the Connect string is set on app open to a global
variable called "gstrODBC". To get a value to sote in gstrODBC, open the
Debug window in your Access app (Ctrl-G) and type this followed by the Enter
key:
?CurrentDb.TableDefs("any_linked_table_name").Conn ect
The returned string should be used as the Connect property for the query
def. For the SQL parameter to SQLExecute, use any valid SQL statement or
query, including stored procedures with parameters. The one caveat is it
must be SQL syntax that the *server* understands, *not* Access's SQL syntax.
The error handler will return messages from the SQL Server if there is a
syntax error, etc.
[vbcol=seagreen]
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:eW0Cw3RuEHA.2116@.TK2MSFTNGP14.phx.gbl...
want[vbcol=seagreen]
> a
> SQL
> In
> data
begun[vbcol=seagreen]
open[vbcol=seagreen]
> a
> add
the
> and
> and
> get
>