Run Query Against ODBC Connected Table VBA

Question

I have a table (readings) already connected by ODBC in Access that opens very quickly when I click on it.
However, when I try to run this in VBA I it locks up and never displays anything:

Dim strSql As String
strSql = "SELECT readings.ids " & _
         "INTO ids_temp " & _
         "FROM readings " & _
         "WHERE readings.ids > 1234;"  //This is id in middle of list
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

For some reason this crashes the whole system. Any ideas?

Answer 1

Let me say that DoCmd.RunSQL is never advisable, particularly with SetWarnings turned OFF, as you have no idea whether the result is what you expect or not. You've told VBA not to report errors, so you never know if all the records were inserted or not.

It's very easy to replace DoCmd.RunSQL with my SQLRun() function, posted here:

How can I get a value from the update query prompt in Access VBA?

Answer 2

Rather than using DoCmd, t's usually handled by your existing connection to create a Command object, which accepts SQL statements to use with the Command.Execute method.

Reading the documentation for DoCmd, it appears to primarily be intended for eexecuting Macros from the Access UI menus.

Answer 3

Does you Database have ids_temp table locally? If ids_temp table is Linked table it will delete the table, because select into CREATES NEW TABLE. If you want to add to table try INSERT INTO command. You can clean table before inserting the data.

Answer 4

So the error was actually my fault the id I was using was causing the Query to return about 6 million results. But, this method actually works great, I just create the table and link a list box on a different form to the table, then I just show the form. I do some closes and updates in between but overall it seems to work well. Thanks for the help