CODEWITHSUNDEEP
sqlms-accessvba
xLokos
xLokos

Reputation: 109

MS Acess button to export query results to Excel

I have a problem with adding a button to a MS Access Form which would execute a query and open the results in a new window (in whatever format).

The two main problems are:

  1. The database is external and password protected
  2. The user cannot see the actual SQL string as they would then get the password to the external database.

The below code unfortunately does not work.

Sub bQuery_Click()

    Dim StrSQL As String
    Dim username As String

    username = Environ("USERNAME")

    StrSQL = "SELECT EntryText, NameID FROM [I:\SharedDrive\Master DB.accdb;PWD=pwd;].Database WHERE NameID='" & username & "';"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, StrSQL, "I:\SharedDrive\file1.xlsx"

End Sub

Upvotes: 0

Views: 418

Answers (2)

Parfait
Parfait

Reputation: 107652

Simply create a query in Access and change the SQL with each button click:

SQL

SELECT EntryText, NameID 
FROM [I:\SharedDrive\Master DB.accdb;PWD=pwd;].Database 
WHERE NameID='xLokos';

VBA

Sub bQuery_Click()    
    Dim qdef As QueryDef

    username = Environ("USERNAME")    
    StrSQL = "SELECT EntryText, NameID" _
               & " FROM [I:\SharedDrive\Master DB.accdb;PWD=pwd;].Database" _
               & " WHERE NameID='" & username & "';"

    Set qdef = CurrentDb.QueryDefs("mySavedQuery")
    qdef.SQL = strSQL
    Set qdef = Nothing         'SAVES QUERY

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
                              "mySavedQuery", _
                              "I:\SharedDrive\file1.xlsx"    
End Sub

Upvotes: 0

Zack
Zack

Reputation: 2341

Would you be able/willing to add the table you're querying as a linked table? That way, you can create the query in your front-end database, and just export that query, rather than having to hard-code SQL in VBA.

Adding the table as a linked table will expose the password on the TableDef object, but it seems like you're already exposing the password in VBA. If you haven't already, you can lock down the database to make it hard for the user to open the VBA IDE (there are a number of strategies to do this, such as creating an ACCDE file, password-protecting the module file, and just locking down ribbons and keyboard shortcuts).

Upvotes: 1

Related Questions