Pass-Through Querys

[Robert]

I have migrated the data and my access application is working fine. If
anything, the querys are running a little slower (37K records). I running
Data Access Pages on Medical Claims Data and would like to use SQL because I
could be in a position where I needed to run the pages on 4 mill records.

What is my best option for getting the speed I need. Since the migration
assistant doesn't use a DSN, I don't seem to be able to use a pass through
query. Is manually building the querys (Their are a lot and they are complex
and easier to build in Access query GUI) my only option?

Any ideas?

[Stefan Hoffmann]

hi Robert,

Post by Robert
What is my best option for getting the speed I need. Since the migration
assistant doesn't use a DSN, I don't seem to be able to use a pass through
query.

This is not correct. You can use a DSN-less connection for passthrough
queries:

http://connectionstrings.com/?carrier=sqlserver

Use one of the ODBC sections.

Post by Robert
Is manually building the querys (Their are a lot and they are complex
and easier to build in Access query GUI) my only option?

When performance is a problem, then you need to move the queries to the
server as views or stored procedures. So the answer is yes, you have to
build them manually.


mfG
--> stefan <--

[Robert]

Thanks- I will give it a shot.

Robert

Post by Stefan Hoffmann
hi Robert,

Post by Robert
What is my best option for getting the speed I need. Since the migration
assistant doesn't use a DSN, I don't seem to be able to use a pass through
query.

This is not correct. You can use a DSN-less connection for passthrough
http://connectionstrings.com/?carrier=sqlserver
Use one of the ODBC sections.

Post by Robert
Is manually building the querys (Their are a lot and they are complex
and easier to build in Access query GUI) my only option?

When performance is a problem, then you need to move the queries to the
server as views or stored procedures. So the answer is yes, you have to
build them manually.
mfG
--> stefan <--

[Robert]

Tried it and it worked great! The speed seems to be there as well. Thanks.

Post by Stefan Hoffmann
hi Robert,

Post by Robert
What is my best option for getting the speed I need. Since the migration
assistant doesn't use a DSN, I don't seem to be able to use a pass through
query.

This is not correct. You can use a DSN-less connection for passthrough
http://connectionstrings.com/?carrier=sqlserver
Use one of the ODBC sections.

Post by Robert
Is manually building the querys (Their are a lot and they are complex
and easier to build in Access query GUI) my only option?

When performance is a problem, then you need to move the queries to the
server as views or stored procedures. So the answer is yes, you have to
build them manually.
mfG
--> stefan <--

[Robert]

I have manually created a saved view (query) in this database and I want to
test it's speed thru a link that is the same type as the wizard creates. I
have copied and pasted the table link, but I don't see any way to modify it
so that it is pointing to the view vs. the table (I don't see any way to do
this). I can't upsize the query because it wasn't able to do it defore.
Since the current link are not DSN's on the system I don't want to mix the
type.

Post by Stefan Hoffmann
hi Robert,

Post by Robert
What is my best option for getting the speed I need. Since the migration
assistant doesn't use a DSN, I don't seem to be able to use a pass through
query.

This is not correct. You can use a DSN-less connection for passthrough
http://connectionstrings.com/?carrier=sqlserver
Use one of the ODBC sections.

Post by Robert
Is manually building the querys (Their are a lot and they are complex
and easier to build in Access query GUI) my only option?

When performance is a problem, then you need to move the queries to the
server as views or stored procedures. So the answer is yes, you have to
build them manually.
mfG
--> stefan <--

[Stefan Hoffmann]

hi Robert,

Post by Robert
I have manually created a saved view (query) in this database and I want to
test it's speed thru a link that is the same type as the wizard creates. I
have copied and pasted the table link, but I don't see any way to modify it
so that it is pointing to the view vs. the table (I don't see any way to do
this). I can't upsize the query because it wasn't able to do it defore.
Since the current link are not DSN's on the system I don't want to mix the
type.

I'm not quite sure what you have done. These are the steps I would
suggest you to do:

1) Create a VIEW on the SQL Server, e.g. dbo.MyView.
2) Test the VIEW in the Query Analyzer or SSMS.
3) Create a linked table (dbo_MyView) in Access using the wizard.
In the wizard you have to select the VIEW. If you can't select it,
then check whether you have the appropriate rights on the SQL Server
to do so.

mfG
--> stefan <--

[Robert]

I'm trying to a link like the one the upsizing wizard creates that is
DSNless. Using the Access link creation wizard doesn't give me this option.

Post by Stefan Hoffmann
hi Robert,

Post by Robert
I have manually created a saved view (query) in this database and I want to
test it's speed thru a link that is the same type as the wizard creates. I
have copied and pasted the table link, but I don't see any way to modify it
so that it is pointing to the view vs. the table (I don't see any way to do
this). I can't upsize the query because it wasn't able to do it defore.
Since the current link are not DSN's on the system I don't want to mix the
type.

I'm not quite sure what you have done. These are the steps I would
1) Create a VIEW on the SQL Server, e.g. dbo.MyView.
2) Test the VIEW in the Query Analyzer or SSMS.
3) Create a linked table (dbo_MyView) in Access using the wizard.
In the wizard you have to select the VIEW. If you can't select it,
then check whether you have the appropriate rights on the SQL Server
to do so.
mfG
--> stefan <--

[Armen Stein]

On Sun, 13 Jan 2008 13:27:01 -0800, Robert

Post by Robert
I'm trying to a link like the one the upsizing wizard creates that is
DSNless. Using the Access link creation wizard doesn't give me this option.

Linking a table or view for the first time using the Access linked
table manager requires a DSN. After the links are created, you can
use the DSN-less technique to use them on a PC without DSNs and
refresh & relink them.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Robert]

I have basically tried this without success. In design mode on linked tables
I can get to ODBC connection string but cannot edit it as I can with a pass
through query. Do you have a link or notes on how to edit a DSN link in
Access?

Post by Armen Stein
On Sun, 13 Jan 2008 13:27:01 -0800, Robert

Post by Robert
I'm trying to a link like the one the upsizing wizard creates that is
DSNless. Using the Access link creation wizard doesn't give me this option.

Linking a table or view for the first time using the Access linked
table manager requires a DSN. After the links are created, you can
use the DSN-less technique to use them on a PC without DSNs and
refresh & relink them.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Stefan Hoffmann]

Post by Robert
I have basically tried this without success. In design mode on linked tables
I can get to ODBC connection string but cannot edit it as I can with a pass
through query. Do you have a link or notes on how to edit a DSN link in
Access?

Aha, that*s your problem:

Public Function TableLinkODBC(ASourceName As String, _
Optional ADestinationName As String = "", _
Optional APrimaryKey As String = "") _
As Boolean

On Local Error GoTo LocalError

Dim CONNECTION_ODBC As String

TableLinkODBC = False

CONNECTION_ODBC = "ODBC;" & _
"DRIVER={SQL Server};" & _
"SERVER=" & Servername & ";" & _
"DATABASE=" & Databasename & ";" & _
"UID=" & Username & ";" & _
"PWD=" & Password

ASourceName = UCase(ASourceName)
If ADestinationName = "" Then
ADestinationName = ASourceName
End If

If TableExists(ADestinationName) Then
Debug.Print "-";
CurrentDbC.TableDefs.Delete ADestinationName
End If

Debug.Print "+"; ASourceName; "="; ADestinationName
CurrentDbC.TableDefs.Append
CurrentDbC.CreateTableDef(ADestinationName, 0, ASourceName, CONNECTION_ODBC)
CurrentDbC.TableDefs.Refresh

If APrimaryKey <> "" Then
SQLExecute "CREATE INDEX pk_" & ADestinationName & " ON " &
ADestinationName & "(" & APrimaryKey & ") WITH PRIMARY;"
End If

TableLinkODBC = True
Exit Function

LocalError:
' Do some error handling here.

End Function


CurrentDbC is the default proxy on CurrentDb, place this in a standard
module:

--
Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If

Set CurrentDbC = m_CurrentDb

End Property
--

mfG
--> stefan <--

[Robert]

I will give this a shot later today. Thanks!

Post by Stefan Hoffmann

Post by Robert
I have basically tried this without success. In design mode on linked tables
I can get to ODBC connection string but cannot edit it as I can with a pass
through query. Do you have a link or notes on how to edit a DSN link in
Access?

Public Function TableLinkODBC(ASourceName As String, _
Optional ADestinationName As String = "", _
Optional APrimaryKey As String = "") _
As Boolean
On Local Error GoTo LocalError
Dim CONNECTION_ODBC As String
TableLinkODBC = False
CONNECTION_ODBC = "ODBC;" & _
"DRIVER={SQL Server};" & _
"SERVER=" & Servername & ";" & _
"DATABASE=" & Databasename & ";" & _
"UID=" & Username & ";" & _
"PWD=" & Password
ASourceName = UCase(ASourceName)
If ADestinationName = "" Then
ADestinationName = ASourceName
End If
If TableExists(ADestinationName) Then
Debug.Print "-";
CurrentDbC.TableDefs.Delete ADestinationName
End If
Debug.Print "+"; ASourceName; "="; ADestinationName
CurrentDbC.TableDefs.Append
CurrentDbC.CreateTableDef(ADestinationName, 0, ASourceName, CONNECTION_ODBC)
CurrentDbC.TableDefs.Refresh
If APrimaryKey <> "" Then
SQLExecute "CREATE INDEX pk_" & ADestinationName & " ON " &
ADestinationName & "(" & APrimaryKey & ") WITH PRIMARY;"
End If
TableLinkODBC = True
Exit Function
' Do some error handling here.
End Function
CurrentDbC is the default proxy on CurrentDb, place this in a standard
--
Option Compare Database
Option Explicit
Private m_CurrentDb As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb
End Property
--
mfG
--> stefan <--