Simple SQL question from newbie

[Yan He]

I am working on setting up a database on Access, since it will involve daily process, I am writing some simple VBA code to do it. I encountered some question when I working on it.

1. When I tried to run the simple code below, I always get an error message "A RunSQL action requires an argument consisting of an SQL statement". Couldn't figure out what kind of argument it means, since I fed the two argumments for RunSQL method already.

Sub Run_SQL()
    Dim SQL_Text As String
    SQL_Text = "SELECT Equity_AE.[LongSecurityName], Equity_AE.[LongExposure] " & _
        "FROM Equity_AE"
    
    DoCmd.RunSQL SQL_Text, False
    
    
    
End Sub

2. I have to import txt files daily to the database, unfortunately, the file name looks like "LondonTribecaRSK514.090407", and there's a dot "." among the file name. So the execution of the code fails. When I took away the dot, it works. But I couldn't change the names of the incoming source files.

:sos::sos::sos:


Thank you.

 

[Adam]

1. DoCmd.RunSQL will only work if the SQL statement being put into the first argument is an action query or a data-definition query. In other words, your SQL statement must be designed to alter the data in the database, not merely display it. As written, you are using a simply SELECT query, which outputs rather than alters data. This is why your statement is failing.

What are you looking to have this Sub procedure do, exactly? Do you want a query to pop up on your screen in Datasheet mode? In that case, I'd write and save the query normally (i.e. not in VBA, but using the regular query window) and then use DoCmd.OpenQuery to open it.

2. Access does not like importing data from files with non-standard extensions. There is no good reason for Access to have this problem, but it does, and it stinks. This leaves you with two options:

a) Write a VBA procedure that opens an instance of Excel, opens the file in Excel, and then loops through the cells in the worksheet, manually adding the data through an open ADO recordset or something. This is not ideal, because ADO and Excel automation run reeeeeally sloooooooooowly...

b) Have your VBA procedure rename (or save a duplicate copy of) the file using a DOS batchfile, replacing the extension with .txt or .csv or whatever's appropriate. Then import the newly renamed (or duplicate) file.

 

[Yan He]

1. DoCmd.RunSQL will only work if the SQL statement being put into the first argument is an action query or a data-definition query. In other words, your SQL statement must be designed to alter the data in the database, not merely display it. As written, you are using a simply SELECT query, which outputs rather than alters data. This is why your statement is failing.

What are you looking to have this Sub procedure do, exactly? Do you want a query to pop up on your screen in Datasheet mode? In that case, I'd write and save the query normally (i.e. not in VBA, but using the regular query window) and then use DoCmd.OpenQuery to open it.

2. Access does not like importing data from files with non-standard extensions. There is no good reason for Access to have this problem, but it does, and it stinks. This leaves you with two options:

a) Write a VBA procedure that opens an instance of Excel, opens the file in Excel, and then loops through the cells in the worksheet, manually adding the data through an open ADO recordset or something. This is not ideal, because ADO and Excel automation run reeeeeally sloooooooooowly...

b) Have your VBA procedure rename (or save a duplicate copy of) the file using a DOS batchfile, replacing the extension with .txt or .csv or whatever's appropriate. Then import the newly renamed (or duplicate) file.

Thanks a lot, Adam. What if I just want to make table using SQL query?

 

[Adam]

In general, I try to avoid DoCmd.RunSQL. Unless you need to dynamically build the SQL statement at run-time, there's nothing you can do with RunSQL that you can't do easier by building the query in the query window and using DoCmd.OpenQuery. (And since queries built and saved in the query window are pre-compiled, OpenQuery will run faster than RunSQL for larger recordsets.)

You can make a Make Table query in the query window by building a regular SELECT query, then choosing Make Table Query from the Query menu option at the top of the screen. Then, use the DoCmd.OpenQuery command in VBA to run it.

 

[Andy Nguyen]

Ah, we have Adam as resident VBA guru here

I recently did something similar but using VBA to connect to a remote MS SQL server using ADO. It works fairly well. I have excel sheet where I enter values into cells, the VBA will take the cells value, put into SQL string, run it and return the data back to another sheet where I can do calculation.

As far as I know, it's the only way to get data without using the query window. Anyone knows any more robust way to get remote data to excel ?