query to linked table timing out

[knyatro]

Hi all! This is my 1st post...

Well, I'm trying to run a query against a linked table from Sage MAS90 in
Access, basically to import only the necessary information so that a series
of queries can be run locally. But it keeps timing out and can't figure it
out. As you see below, I set the timeout to be 120, but I guess the timeout
for "linked" table is set by something else.....any thoughts?? Thanks in
advance!

'Set query time out to be 120 seconds

Dim mydb As Database
Set mydb = DBEngine(0)(0)
mydb.QueryTimeout = 120

'Declare SQL statement for making a local table

Dim strSQL As String

TempIM2SQL = "SELECT fields" & _
"INTO TBL_Local" & _
"FROM TBL_Linked "

'Run a make table query

DoCmd.RunSQL strSQL

'Run a sub to assign primary key
Call AssignPK

 

[Steve Sanford]

Welcome,

The first thing to do is to press <control>-G to open the IDE, then click on
TOOLS/OPTIONS. In the EDITOR tab, all check boxes should be checked -except-
the first one: "Auto Syntax Check".

The main thing is to have the "Require Variable Declaration" option checked.
This will add the line "Option Explicit" at the top of of every NEW module
created. Also, you should add "Option Explicit" at the top of of every module
you have now.

One of the problems with the code is that you declared "strSQL" as a string
for the make query, but you used "TempIM2SQL" to hold the "Select...."
statement.

I have never had to use the "QueryTimeout" property....... AFAIK, you
shouldn't have to do that with linked tables.

If you are going to import records often, it would be better to create the
table and set the indexes one time, then use a delete query to remove all of
the records before running an append query to import new records. Less bloat
importing than creating a new tables each time.

Your make table query has only one field????

I modified your code but haven't tested it. I created a button named
"ImportData".
Here is the code:

'------------------
Private Sub ImportData_Click()
On Error GoTo Err_ImportData_Click

'Set query time out to be 120 seconds

Dim mydb As Database
'Declare SQL statement for making a local table
Dim strSQL As String


' Set mydb = DBEngine(0)(0)
Set mydb = CurrentDb

' mydb.QueryTimeout = 120

' TempIM2SQL = "SELECT fields" & _
' "INTO TBL_Local" & _
' "FROM TBL_Linked "

strSQL = "SELECT fields" & _
"INTO TBL_Local" & _
"FROM TBL_Linked "

'Run a make table query
' DoCmd.RunSQL strSQL
mydb.Execute strSQL, dbFailOnError

'Run a sub to assign primary key
Call AssignPK

'
' More code???
'

Exit_ImportData_Click:
Exit Sub

Err_ImportData_Click:
MsgBox Err.Description
Resume Exit_ImportData_Click

End Sub
'------------------


HTH

 

[knyatro via AccessMonster.com]

Hi Steve,

Thanks for your awesome reply.

Also thanks for pointing out a few grammatical mistake. I edit it to make
easier for others to read and failed big time. Also for the SQL string, i
just used a word "fields", just to save hassle. Sorry.

Anyhow....

You made a good point about removing table contents and append to it. It's
not shown but my current codes will drop the table before running the query
every time. I'll try your way.

Option Explicit was a good help. I found a few errors in subs for dropping
the table...and maybe that was causing the time out. so many big Thanks!

I'll test out the codes for a little while and see...if it made a difference.

now...if I want to run these codes at certain time of the day, would you
think the Timer function be appropriate?

 

[John W. Vinson]

now...if I want to run these codes at certain time of the day, would you
think the Timer function be appropriate?

Better would be to open the database (perhaps with an /x macroname command
line switch to run a macro which calls your routine) from the Windows
Scheduler.

 

[knyatro via AccessMonster.com]

i see. that's much easier than reinventing a wheel. Great. The queries are
also running without anymore time out now. Thanks a whole bunch!