VBA connecting to SQL?

Question

I am writing a VBA macro for Excel that connects to my SQL database, collects some data, returns it to the spreadsheet, and then formats it. The problem i am encountering is that the macro proceeds with the instructions while the SQL query is still loading data. I have attempted to pause the macro with an "Application.OnTime" but it is not working too well. I have also tried inputbox or msgbox, but those both seem to pause the query as well as the macro. Any ideas besides using two different macros?

Answer 1

If you are using the QueryTables approach to connecting to SQL, you need to set the .Refresh BackgroundQuery:=False to allow the query to complete before proceeding with the macro. You could probably also use a Refresh wait loop, but the first option should work fine.

Answer 2

VBA and SQL are different things. VBA is a full-blown programming language that you can use to get Access (and other Microsoft Office programs) to do just about anything you want. SQL is a language used exclusively to manipulate the data and structure of a database.



VBA calls on a vast range of objects, properties, methods, functions and constants to construct the sometimes complex statements used to control the program. SQL uses a limited range of "keywords" combined with information you supply, such as table names, field names and criteria, to construct essentially simple (although sometimes long) statements detailing what you want the program to do with your data.



SQL can often be used alone, for example when setting the RecordSource property of a form or report in design view, or when working in the SQL View of the Access query design window. But this tutorial is about how to combine SQL with VBA. Its aim is explain the rules of SQL and to encourage you to use good code-writing practice and to avoid the pitfalls and problems that can occur. The way you write your code is a very personal thing so I'm going to show you how I do things, using some techniques I have learnt from others and some I've figured out myself. You don't have to do it my way, but it works for me so it's what I teach!



VBA is a very flexible language and there are often many different ways in which VBA can achieve the same task. SQL on the other hand is a very precise and inflexible language. Everything has to be just so, but it has the advantage of also being very simple. As I explained in Part 1: Setting the SQL Scene there are several dialects of SQL. Here I will be using the Jet SQL that drives the Access database engine.



For more queries click the link ps. study only part 1

Answer 3

Just off the top of my head....how about a loop that continues until the query has loaded? This is just pseudo code...and I have the time checking included so you don't go into an infinite loop.



x = now + 5 minutes



Do while recordset is nothing and time < x



loop



if recordset is nothing then

msgbox "Timeout waiting for SQL Server"

end if

Answer 4

attempt to remodel them to unix timestamp format (form of seconds from January 1st, 1970). there might want to be a function that does this for you (and reduce back from it). then the evaluation is user-friendly, you in basic terms evaluate integers, and also you are able to genuinely do date manipulations (ie a million day = 86400 seconds)