Populating large data volumes from server 1

[Janice7]

I'm running a query which is populating a large amount of records from a table sitting on a server elsewhere. The query gave me an "ODBC - call failed" due to the excess amount of data volume.

My knowledge on the database connectivity side is limited, is there anything I can do as a client to make this query run?

 

[Steve101]

Are you sure that the failure is caused by an "excess amount of data". With ODBC its more likely to be a "timeout" problem. In your query, ensure that the ODBCTimeout property is set to 0. This will allow the query to run indefinately (unless the DB administrator at the server end has placed limitations on the database).

A much better approach, is to find out about "passthrough queries". This allows the query to run entirely on the server (using the server's SQL engine), and only pass back its results to Access.

Hope this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Janice7]

Thank you. I think you might be right. However, the queries are built within modules. So I'm not sure how I can re-set the ODBC property to 0. I've tried the following:

Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb

' Change the default QueryTimeout of the Northwind
' database.
Debug.Print "Default QueryTimeout of Database: " & _
dbsCurrent.QueryTimeout

dbsCurrent.QueryTimeout = 0

Debug.Print "New QueryTimeout of Database: " & _
dbsCurrent.QueryTimeout

but it gives me an error at the bolded line saying "User Defined Type Not Defined" Any suggestions?

Thanks

 

[Steve101]

It would help if you could publish a bit of the code where the queries are built and applied. Its at this point that the modificiation will be required.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Steve101]

It would help if you could publish a bit of the code where the queries are built and applied. Its at this point that the modificiation will be required; probably using a Querydef object. Send the code and we'll have a look.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Janice7]

Thanks. Well here's what I have so far. I got a command button on a form which has the following VBA code linked to the "on click" property.

Private Sub cmdButton1_Click()

'Declare SQL string
Dim strSQL as String
Dim strSQLinsert, strSQLselect, strSQLfrom as String

strSQLinsert = "INSERT ... ... "
strSQLselect = "SELECT ... ..."
strSQLfrom = "FROM ... ..."

strSQL = strSQLinsert & strSQLselect & strSQLfrom

DoCmd.RunSQL strSQL
End Sub

Basically, it just creates & executes a SQL statement. So how would you go about re-set the ODBC Timeout to 0??

Thanks

J

 

[Steve101]

Janice,

The queries you are building are NOT "passthrough" queries, so if you are accessing a remote server and processing a lot of records, be aware that ALL processing will be happenning on your PC and travelling over the network. This can really block things up.

Can you identify the type of server that is at the other end of your query. The idea of "passthrough" queries, is that you get the database server to do all of the work resolving the query, and then send only its results back to the client. In order to do this, you use the "passthrough" query type.

To define a passthrough query, do the following:

(a) In Query design view, create a new query with no tables.
(b) From the menu, select Query, SQL Specific, PassThrough options from the menu.
(c) Type appropriate SQL (for the target server) into the query window.
(d) Select the View, Properties options and set the queries ODBCTimeOut property to zero.

When you need to run the query, use the following command

docmd.openquery "qryYourQueryName"

There's actually a lot more you can do programmatically, but hopefully this has given you a bit of a flavour.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Janice7]

Steve

I understand what you're saying, and your solution definitely makes sense. But the reason I need to build the query is Visual Basic is that I have variables within the SQL statement that come from codes in other modules. For example:

Private Sub cmdButton1_Click()

'Declare SQL string
Dim strSQL as String
Dim strSQLinsert, strSQLselect, strSQLfrom as String

strSQLinsert = "INSERT ... ... "
strSQLselect = "SELECT ... ..." & strVariable & "... ..."
strSQLfrom = "FROM ... ..." & strAnotherVariable "..."

strSQL = strSQLinsert & strSQLselect & strSQLfrom

DoCmd.RunSQL strSQL
End Sub

where strVaraible and strAnotherVariable is declared as Global variables in another module. I didn't know how to access these variables from the Query Design View in the GUI, so I had to build this subroutine in VBA.

So I'm not sure if you have another idea that would allow me to set the ODBC Timeout property in VBA.

Let me know if you have questions

Thanks for all the help =)

Janice

 

[Steve101]

Janice,

Check out the following thread. thread181-335943

In the above thread, I've provided some instructions on how to set up a passthrough query and then programatically change the SQL. This will give you the capability that you want, as per your above example.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au