[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

Running access query with specs to .txt file

I have a form in Access 2010 that runs the following line of code when a button is pressed:

DoCmd.TransferText acExportDelim, "mySpecs", "myQueryName", "C:\myFileName.txt", False

I wish to use Excel and perform this same exact functionality using VBA within Excel.

I am able to run the query and have the output in Excel just fine, but I need to use the specs that are saved within the database.  Any ideas?

Thanks!
0
mak345
Asked:
mak345
  • 2
  • 2
1 Solution
 
Rgonzo1971Commented:
HI,

pls try

Dim acObj As Object
Set acObj = CreateObject("Access.Application")
acObj.Application.Visible = True
acObj.OpenCurrentDatabase "C:\testMDB\TEST.accdb",,"ADatabasePassword"
acObj.DoCmd.TransferText acExportDelim, "mySpecs", "myQueryName", "C:\myFileName.txt", False
acObj.Application.Quit
Set acObj = Nothing

Open in new window

Regards
0
 
aikimarkCommented:
You can use Access automation from within Excel.  It would look something like this:
Dim oACC as object
dim oDB as object
set oACC = Createobject("access.application")
oACC.Opendatabase "your database path and name"
oACC.DoCmd.TransferText acExportDelim, "mySpecs", "myQueryName", "C:\myFileName.txt", False
oACC.Close
oACC.Quit
set oACC = nothing

Open in new window


You could also populate an ADO recordset variable in Excel and do your exporting directly from the recordset or through an intermediate variant array (populated with GetRows method)
0
 
mak345Author Commented:
Using either of the above code, I get the following message:

The Microsoft Access database engine could not find the object 'myFileName.txt'.  Make sure the object exists and that you spell its name and the path name correctly.  If 'myFileName.txt' is not a local object, check your network connection or contact the server administrator.
0
 
aikimarkCommented:
Since you're in  the Excel environment, the acExportDelim constant is probably unknown.
Dim oACC as object
dim oDB as object
const acExportDelim as long = 2
set oACC = Createobject("access.application")
oACC.Opendatabase "your database path and name"
oACC.DoCmd.TransferText acExportDelim, "mySpecs", "myQueryName", "C:\myFileName.txt", False
oACC.Close
oACC.Quit
set oACC = nothing

Open in new window

0
 
mak345Author Commented:
Thank You!!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now