Re: Export Excel To Access Wizard HELP!!!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,

Depending on what your Excel code is doing and what's in your queries,
it's often simplest to cut Access out of the loop entirely and instead
write Excel code that uses the DAO library to open the secured database
and execute the queries. I've pasted sample code at the end of this
message to show the sort of thing that's involved.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;192919


On Wed, 29 Jun 2005 13:46:02 -0700, "tmj00345"
<tmj00345@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>This isn't a reply. It's the start of a related message. Why? Because after
>signing on with my username and password, I am unable to start a new thread.
>Clicking the NEW button causes a "hickup"-like warning beep, and then doesn't
>do anything else. This is an intermittent problem. Sometimes it hickups,
>sometimes it works. But I can post a reply to someone else's message, so
>here I am.
>
>So what's my issue? I have an Access app which contains queries used by an
>Excel spreadsheet to refresh its data on open. It worked fine.....until I
>rashly thought to add user-level security to my Access app. After reading
>stuff by MS on its user-level security, as well as a couple of books by
>Access MVPs, I thought I was ready. Even so, just to remain as plain vanilla
>as possible, I did everything through the security wizard. I set up my user
>in the pre-defined group that allows data adds, changes and deletes, but
>nothing else. I set up myself in the Admins group.
>
>Access is closed now, and security seems to work fine. So I open Excel and
>click on the "allow refresh" message (forget the exact text). I get a message
>saying I don't have permission to use the queries that do the refresh.
>Puzzling. How does Excel know who I am? It didn't provide a logon screen, so
>I never told it my logon name, never provided my password.
>
<snip>

The sample procedure below opens a secured mdb and will work with minor
modifications in VBScript, VB and VBA.

If you actually have to automate Access (e.g. to if the queries contain
custom VBA functions), see e.g.
How To Automate a Secured Access Database Using Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;EN-US;192919

Dim dbE 'As DAO.DBEngine 'Remove the apostrophes
Dim dbW 'As DAO.Workspace 'to change from late to
Dim dbD 'As DAO.Database 'early binding.
Dim rsR 'As DAO.Recordset

Set dbE = CreateObject("DAO.DBEngine.36") 'VBS, late binding
'Set dbE = New DAO.DBEngine 'VB/VBA, early binding

dbE.SystemDB = "C:\temp\xx\secured.mdw"
Set dbW = dbE.CreateWorkspace("Secured", "UserName", "Password",2)
Set dbD = dbW.OpenDatabase("C:\temp\xx\test.mdb")

Set rsR = dbD.OpenRecordset("Query1")
With rsR
MsgBox .Fields(0).Value 'demo only
'Add a record:
' .AddNew
' .Fields(FileName).Value = strResultsFileName
' ...other fields if needed...
' .Update
End With

rsR.Close
dbD.Close
dbW.Close
Set rsR = Nothing
Set dbD = Nothing
Set dbW = Nothing
Set dbE = Nothing



'Here's one that opens one secured database and creates an unsecured
one:

Const SystemDB = "D:\Folder\blah.mdw"
Const SecuredDB = "D:\Folder\Secured.mdb"
Const UnsecuredDB = "D:\Folder\Unsecured.mdb"
Const UserName = "User"
Const Password = "Password"

Dim dbESec 'As DAO.DBEngine
Dim dbWSec 'As DAO.Workspace
Dim dbDSec 'As DAO.Database

Dim dbE 'As DAO.DBEngine
Dim dbDNew 'As DAO.Database

Set dbESec = CreateObject("DAO.DBEngine.36")
dbESec.SystemDB = SystemDB
Set dbWSec = dbESec.CreateWorkspace("Secured", _
UserName, Password, 2) '2 = dbUseJet
Set dbDSec = dbWSec.OpenDatabase(SecuredDB)

Set dbE = CreateObject("DAO.DBEngine.36")

'Add code here to make sure UnsecuredDB doesn't
'already exist

Set dbDNew = dbE.CreateDatabase(UnsecuredDB, _
";LANGID=0x0409;CP=1252;COUNTRY=0")
'above string = dbLangGeneral

'Token manipulation of DBs to prove they're available
Msgbox dbDSec.Name & ": " & dbESec.SystemDB _
& Chr(13) & Chr(10) & dbDNew.Name & ": " & dbE.SystemDB

'Add code here to execute SQL queries
...

'Tidy up
dbDSec.Close
dbWSec.Close
dbDNew.Close

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Relevant Pages

  • Re: Code looping through files
    ... Dim myName As String ... Sub AllFolderFiles() ... Professional and I am using Excel 2003 Professional with Windows XP Media ... in a folder, opens each, does things, closes the file ...
    (microsoft.public.excel.programming)
  • Re: Check File Open Macro
    ... Dim oXL As Object 'This is needed to open a new instance of Excel. ... 'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL. ...
    (microsoft.public.excel.programming)
  • Re: Code looping through files
    ... Otto posted this code as the basis for the code you're using. ... Dim TheFile As String ... Professional and I am using Excel 2003 Professional with Windows XP Media ... in a folder, opens each, does things, closes the file and ...
    (microsoft.public.excel.programming)
  • Re: Automation question
    ... Microsoft Excel Object Library for whatever version Office you have. ... It then checks the collection of workbooks and grabs a reference to ... the one to be edited if its already running, opens it if it isn't. ... Dim oOpenBook As Excel.Workbook ...
    (microsoft.public.excel.programming)
  • Re: Automation question
    ... Microsoft Excel Object Library for whatever version Office you have. ... It then checks the collection of workbooks and grabs a reference to ... the one to be edited if its already running, opens it if it isn't. ... Dim oOpenBook As Excel.Workbook ...
    (microsoft.public.excel.programming)