UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Export To Excel Error 3011 Db Linked In Refernces, Access 2013    
 
   
Nailz
post Aug 16 2019, 09:33 AM
Post#1



Posts: 13
Joined: 9-May 13



Hello

I have a procedure, in a module, in my FE (accdb). It creates a query, and exports to an xlsx file that I do have access to. The procedure works with no issues.

I have been asked to allow other users also perform this same output. I created a new DB with a single form and a single button that calls to this procedure, just a different name.

After doing some research I found that by referencing the BE, to the FE, under Tools>Reference I was able to create a link between the two DB (to call the procedure).

I have finally been able to create queries, search, and delete those queries as well all while it remains closed. Originally I was using the AppAccess.Run command, but it was opening the BE. Passing variables seemed difficult as well.

This creation of a new DB goes behind security issues accessing data from link tables. I know I can hide the NavPane and a few other things, but this seemed best at the moment.


ISSUE: I am getting an error 3011 “…could not find the object ‘query1’. Make sure it exists…’

The debugger is highlighting here:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query1", "z:\myExportLocation\myExportFile.xlsx", True, "query1"


Command button the code on that single form in FE:

Dim Driveletter As String
Dim StartDate As Date
Dim FY As String
Dim EndDate As Date

Driveletter = InputBox("Specify the drive letter you designated as the Shared Drive", "Attention", "Z:")
StartDate = InputBox("Specify the start date you wish to use", "Attention", "10/1/2018")
EndDate = InputBox("Specify the end date you wish to use", "Attention", "9/30/2019")
FY = InputBox("Specify the name of the FY you wish to use", "Attention", "20")

Call myExportProcedure(Driveletter, StartDate, EndDate, FY)

In the procedure, that is called in the BE, I am using (snippet):

Public Sub myExportProcedure (ByVal DriveLetter As String, ByVal FY As String, ByVal StartDate As Date, ByVal EndDate As Date)

‘declarations
Dim NotCurrentDb As DAO.Database
Set NotCurrentDb = DBEngine.Workspaces(0).OpenDatabase("C:\myDbBe.accdb")

Dim qdf As DAO.QueryDef
Dim query As String

‘find queries and delete. working!

For Each qdf In NotCurrentDb.QueryDefs
If qdf.Name = "query1" Then
DoCmd.DeleteObject acQuery, "query1"
Else
End If
Next

‘create query. working!
query = "SELECT [field1], [field2] FROM tbl1”

Set qdf = NotCurrentDb.CreateQueryDef("query1", query)

'export query. not working!
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query1", "z:\myExportLocation\myExportFile.xlsx", True, "query1"


I have been about a week at this, and to no avail through trial and error or search results. If you can provide and answer, recommendation, or a link, it would be greatly appreciated.!


MS Access Version: 2013 (64bit)

References in both DB (FE/BE)
-VB for Applications
-MA 15.0 Obj Lib
-OLE Automation
-MO 15.0 Access DB Engine Obj
-MS Excel 15.0 Obj Lib
-MS VB for Applications Ext 5.3
- C:\myDbBe.accdb (only in FE refernces)

Go to the top of the page
 
 
Start new topic
Replies
theDBguy
post Aug 16 2019, 09:56 AM
Post#2


UA Moderator
Posts: 77,704
Joined: 19-June 07
From: SunnySandyEggo


Hi. If the code you're using is the same as the one you want others to use as well, then rather than referencing the BE, why not just link to it? I'm not sure I fully understand what you're trying to do.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Nailz
post Aug 16 2019, 10:15 AM
Post#3



Posts: 13
Joined: 9-May 13



Hello

Thanks for replying. I was looking for ways to avoid users from having access to the linked tables. I could hide the Nav Pane and do a few other things. I found someone linking the DBs by referencing it in the VB refernces and wanted to see how that worked. It worked up until exporting the files. I had planned on then putting a PW on the VB so no one could further enter any code.

The procedure produces an excel report based up some variable entered. These users would like to see the same report. I just didn't want to give them access to the same DB I use.
Go to the top of the page
 
theDBguy
post Aug 16 2019, 10:34 AM
Post#4


UA Moderator
Posts: 77,704
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you can get your current setup to work, then great. I'm not sure I can help you with it without seeing it because I can't imagine what it is you're trying to do. Otherwise, you might still be able to deploy a "locked down" database to your users with a single purpose of exporting the data they need into Excel but without letting them mess with the code or access the data any other way. There are many techniques you can use to achieve this. So, if you want to continue with your original approach and still need assistance, perhaps consider posting a small copy of your setup, so we can take a look and advice you of a way to go. Or, if you want to try it another way, then let us know, and we'll provide some options you could try out. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    29th March 2020 - 06:58 AM