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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> 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
 
theDBguy
post Aug 16 2019, 09:56 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,604
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


Access Wiki and Forums Moderator
Posts: 76,604
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
 
ADezii
post Aug 16 2019, 11:19 AM
Post#5



Posts: 2,690
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. You are attempting to Open an External Database, create a Query within it, then Export that Query as an Excel Spreadsheet from within the context of the Current Database. I don't think that you can do that. What you can do is to:
    1. Create a New Access Application using Early Binding (can also use Late Binding if so desired).
    2. Open the External Database within the context of the Current Database, as the Current Database, using the OpenCurrentDatabase() Method.
    3. Create the Query.
    4. Export the Query as an Excel Spreadsheet.
    5. Close the Instance of the External Database.
    6. Do some Clean Up chores.
  2. With this approach, the External DB is NOT linked to any FE, but exists solely on its own.
  3. I'm currently in work using an older Version of Access so you would need to adjust the Code accordingly.
  4. The Code has been tested and is operational.
  5. Code definition:
    CODE
    Dim appAccess As Access.Application
    Dim qdf As DAO.QueryDef
    Dim qry As String
    Dim query As String

    Set appAccess = New Access.Application

    With appAccess
      .OpenCurrentDatabase "C:\Test\Test.mdb", True

       query = "SELECT [Field1], [Field2] FROM tbl1"

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

       .DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", _
                                  "C:\Test\MyExportFile.xls", True

       .CloseCurrentDatabase
    End With

    appAccess.Quit
    Set appAccess = Nothing
    Set qdf = Nothing

This post has been edited by ADezii: Aug 16 2019, 11:21 AM
Go to the top of the page
 
Nailz
post Aug 16 2019, 12:12 PM
Post#6



Posts: 13
Joined: 9-May 13



theDBguy

I think putting up a similar copy may help. I know it's sometimes hard to understand what others are trying to convey. Of course maybe there are other work around's after seeing it. I will post this afternoon or tomorrow if you would like to look.

I was still pursuing to search when I found the: DoCmd.OutputTo acOutputQuery, "query1", acformatxlx, "z:\myExportLocation\myExportFile.xlsx", True, "query1"

This allowed me to export the query with no issues. So it seems the problem may lie with transfer method itself while the DB is closed.

Go to the top of the page
 
Nailz
post Aug 16 2019, 12:17 PM
Post#7



Posts: 13
Joined: 9-May 13



ADezii

Thank you for replying. I will add this in and give it a shot. Does the DB that contains the tables, open?

"b. Open the External Database within the context of the Current Database, as the Current Database, using the OpenCurrentDatabase() Method."

I was avoiding the "other users" from having access to view the tables, well more like able to edit.

I will let you know how it turns out and thanks!

Go to the top of the page
 
Nailz
post Aug 17 2019, 11:38 AM
Post#8



Posts: 13
Joined: 9-May 13



I played around with with some recommendations in the below posts and did get it to work. The two DBs are no longer linked through the Tools>References. The original Excel error was fixed by by trial and error to only allow me to run and Excel Export, but it only allowed me export one query and I needed multiple queries. In the end I made a few changes and now it allows me to Transfer Spreed Sheet, which I can now pass multiple ones as needed.

I am thinking once I put a PW on the VB in the User DB, no one should be able to access the tables??

I placed this in the "User" DB, behind the Command Button (snippets):

Dim appAccess As Access.Application
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\MyLocation\Link_Server.accdb", False, "1234"
appAccess.Run "GetReport", intVar1, intVar2
Set appAccess = Nothing

In the DB where are my tables are, is the procedure "GetReport", that runs this and places the queries in the Excel Spread Sheet (snippets):

Sub GetReport(intVar1 As Integer, intVar2 As Integer)

On Error GoTo Error_Handler

Dim qdf As QueryDef

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

If qdf.Name = "query2" Then
DoCmd.DeleteObject acQuery, "query2"
Else
End If
Next

Dim query As String

'query1
query = "select color, " & intVar1 & " as Var1 from tblcolors"

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

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query1", "C:\myLocation\linkuser.xlsx", True, "query1"

'query2

query = "select color, " & intVar2 & " as Var2 from tblcolors"

Set qdf = CurrentDb.CreateQueryDef("query2", query)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query2", "C:\myLocation\linkuser.xlsx", True, "query2"

Resume_Exit:
Exit Sub

Error_Handler:
MsgBox "Error Number:" & Err.Number & vbCrLf & _
"Error Description:" & Err.Description

End Sub

Attached are the "test" DBs and Excel I completed this on.

Attached File(s)
Attached File  Link_User.zip ( 21.16K )Number of downloads: 1
Attached File  Link_Server.zip ( 21.16K )Number of downloads: 2
Attached File  linkuser_Excel.zip ( 6.52K )Number of downloads: 1
 
Go to the top of the page
 
Nailz
post Aug 17 2019, 11:44 AM
Post#9



Posts: 13
Joined: 9-May 13



ADezii

It looks like I got it to work. I think. I posted above. I was able to call to the other DB, and have it remained closed, and pass the queries to an Excel sheet. This allows me to keep the other DB secure. Thanks for your help!

Go to the top of the page
 
Nailz
post Aug 17 2019, 11:46 AM
Post#10



Posts: 13
Joined: 9-May 13



theDBguy

I think I got it to work. I posted the example DBs and Excel. If you see and security vulnerabilities or anything I should consider, I welcome it! Thanks for your help as well.
Go to the top of the page
 
theDBguy
post Aug 17 2019, 11:59 AM
Post#11


Access Wiki and Forums Moderator
Posts: 76,604
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.

--------------------
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
 


Custom Search


RSSSearch   Top   Lo-Fi    21st November 2019 - 11:50 AM