Newbie to VBA for Access - pass query data to VB Variable 1

[chrish47]

I am trying to rename a file and save it based on some data in one of the queries contained in Access. I have the Database built, and most of the functions are in Access itself, but I want to set it up to open a file from a specific folder (which I have done with no problems), then copy the file to a Completed folder and rename it with two fields from a query.

I have a query that has one row with two columns of data, the month and year of the data. I want to have VB rename the file so that it changes it from UseTax.xls to UseTax-Oct-2003.xls and saves it to a different folder.

Copying the file and renaming it are no problem, but how can I access this query data from VB?

I have the query set up as FILE DATA, and the above info is all it will contain for each month (OCT and 2003 in the above example). I know that this should be very simple, but I am having problems just figuring out how to access the data records in the queries and tables (as I said I am new to VBA for Access). Any help would be greatly appreciated.

If more info is required let me know and I can provide it.

Thanks,
Chris Huss
CWU Accounting

 

[VBslammer]

Sub GetExcelFileName()
  Dim strFileName As String
  
  With CurrentProject.Connection.Execute("FILE DATA", , 0)
    strFileName = "UseTax-" & .Fields("Month") & "-" & .Fields("Year") & ".xls"
    Debug.Print strFileName
  End With
  
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[chrish47]

I tried the code supplied above and I get the following error:
"runtime error '-2147217900 (80040e14)':
Invalid SQL Statement; expected 'DELETE', 'INSERT','PROCEDURE', 'SELECT', or 'UPDATE'."

I tried changing the following line as follows:
With CurrentProject.Connection.Execute("FILE DATA", , 0)
to
With CurrentProject.Connection.Execute("SELECT FILE DATA", , 0)
and then I get a "Syntax Error (missing operator) in query expression 'File Data'.

Any idea what I am doing wrong? I am using Access 2000. I know that there should be a simple way to access the data contained in queries from VB, but I am not familiar with how to do that from VB. Any help would be greatly appreciated.

Thanks,

 

[VBslammer]

I forgot to mention you that when you have a space in your query name you have to enclose it in brackets:

  With CurrentProject.Connection.Execute("[FILE DATA]", , 0)
    strFileName = "UseTax-" & .Fields("Month") & "-" & .Fields("Year") & ".xls"
    Debug.Print strFileName
  End With

VBSlammer

Unemployed in Houston, Texas

 

[chrish47]

Thanks, That worked great.

I had a workaround with using a Make Table Query and then looking at the table (which didn't have any spaces in the name) which worked, but it is much easier doing it as you indicated.

Thanks again.