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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Open .accdb Database Query With Word VBA, Office 2010    
 
   
doctor9
post Jun 5 2017, 01:35 PM
Post#1


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


I am trying to open a query within an .accdb file, and I keep getting this error:
QUOTE
Run-time error '3343':
Unrecognized database format 'C:\Frontends\MTETesting_FE.accdb'.


I've done some Googling, but nothing is working yet. Hoping someone has some insights. Based on one post I found, I've tried disabling the Microsoft DAO 3.6 Object Library and using the Microsoft Access 14.0 Object Library instead, but neither seems to be working. Here's my basic code:

CODE
    Dim db As Database
    Dim ws As Workspace
    Dim rst As Recordset
    
    ' Connect to Database
    Set ws = CreateWorkspace(Name:="JetWorkspace", _
        UserName:="admin", Password:="", UseType:=dbUseJet)
        
    Set db = OpenDatabase("C:\Frontends\MTETesting_FE.accdb")     '<--- Error 3343: unrecognized database format
    
    Set rst = db.OpenRecordset("qryPAVSampleLabels")
    
    Do Until rst.EOF
        Debug.Print rst.Fields(0) & ": " & rst.Fields(1)
        rst.MoveNext
    Loop
    
    rst.Close
    db.Close
    ws.Close


Anyone successfully connected to an .accdb file to retrieve data from within Word VBA?

BTW, this is not a mail merge - I need to grab the values from this query and put them in a listbox on a userform. Then, the user will select which records they want to pull more data about.

EDIT: The above code is using the DAO 3.6 reference. Here's what I've tried using with the Access 14.0 Object Library instead, based on advice I've seen online:

CODE
    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Dim rst As DAO.Recordset
    
    ' Connect to Database
    Set ws = DBEngine.Workspaces(0)
        
    Set db = ws.OpenDatabase("C:\Frontends\MTETesting_FE.accdb")


This just gives me a compile error for the Dim db line, saying the user-defined type is not defined. Several places seem to think this should work, though.

EDIT 2: I could use ADO instead of DAO - I'm not picky if it works. Looks like this might be a good lead, but I'm still curious if I'm just doing something fundamentally wrong.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Doug Steele
post Jun 5 2017, 03:02 PM
Post#2


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


The following worked for me (Word 2013 to Access 2013 using Microsoft Access 15.0 Object Library):

CODE
Dim db As Database
Dim rst As Recordset
             
    Set db = DBEngine.OpenDatabase("C:\Frontends\MTETesting_FE.accdb")
    Set rst = db.OpenRecordset("qryPAVSampleLabels")
    
    Do Until rst.EOF
        Debug.Print rst.Fields(0) & ": " & rst.Fields(1)
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing

Note that I removed the reference to ws, because I didn't think it added anything.

If you decide you do want to create the workspace, you need to change your OpenDatabase statement to

CODE
    Set db = ws.OpenDatabase("C:\Frontends\MTETesting_FE.accdb")

(In the spirit of total honesty, the code I posted didn't actually work as-is: I had to use a different database, and a different query name. laugh.gif )

--------------------
Go to the top of the page
 
DanielPineault
post Jun 5 2017, 03:09 PM
Post#3


UtterAccess VIP
Posts: 5,454
Joined: 30-June 11



Dennis,

I quickly tried the following and it works for me. Fingers crossed it works for you as well!

CODE
Public Function Access_RunQry(ByVal sDBName As String, Optional sdbPwd As Variant) As Integer
    On Error GoTo Err_SetProperties
    Dim ws                    As DAO.Workspace
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim i                     As Integer

    Set ws = DBEngine.Workspaces(0)

    If IsMissing(sdbPwd) = True Or IsNull(sdbPwd) = True Then
        Set db = ws.OpenDatabase(sDBName)
    Else
        Set db = ws.OpenDatabase(sDBName, False, False, "MS Access;PWD=" & sdbPwd)
    End If

    Set rs = db.OpenRecordset("Query1")
    With rs
        For i = 0 To rs.Fields.Count - 1
            Debug.Print rs.Fields(i).Name
        Next
        If rs.RecordCount <> 0 Then
            Do While Not .EOF
                Debug.Print rs.Fields(0) & ": " & rs.Fields(1)
                .MoveNext
            Loop
        End If
    End With

Exit_SetProperties:
    Set db = Nothing
    Exit Function

Err_SetProperties:
    SetProperties = False
    MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf & Err.Description
    Resume Exit_SetProperties
End Function

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
doctor9
post Jun 5 2017, 03:34 PM
Post#4


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Doug - Thanks! I tried your code, but even with the Microsoft Access 14.0 Object Library checked, I still get the first DIM line highlighted, with the "user-defined type undefined" error. I feel like something this simple SHOULD work, but I'm stuck as to what I'm doing wrong.

Daniel - Thanks to you as well! Unfortunately, I get the "unrecognized format" error with your code. For the record, my frontend doesn't have a password, so it's not that bit.

Both of these suggestions look like they ought to work for me, but I'm just stuck as to why they aren't.

The good news is that SerranoG's code actually works for me - I just had to figure out which Reference to check. Turned out to be Microsoft ActiveX Data Objects 2.8 Library for anyone reading this thread in the future. ADO is not what I normally work with, but hey... if it works, I can move on with my life. Still wouldn't mind understanding what's different between my setup and the above two suggestions though...

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Doug Steele
post Jun 5 2017, 03:36 PM
Post#5


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Dennis: Did you type the code out, or copy-and-paste it from somewhere? I found I got a number of odd errors when I copy-and-pasted it, but it worked fine when I typed it in.

--------------------
Go to the top of the page
 
DanielPineault
post Jun 5 2017, 04:02 PM
Post#6


UtterAccess VIP
Posts: 5,454
Joined: 30-June 11



I developed the code on 2013, but just tested on 2010 and it ran successfully for me on an accdb (no password). I suspect something else is wrong here.

What about trying an Office Repair?
I'm assuming everything compiles fine?
The database itself is fine? You can open and work with it normally?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
doctor9
post Jun 5 2017, 04:43 PM
Post#7


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Doug - I copy/pasted the code, then added the Access reference before running it. Out of curiosity, Did the errors you get have anything to do with the leading spaces not acting like regular old spacebar-generated spaces? I've seen that happen on ONE user's computer, but he's a weird exception. He works from home and travels a lot, so our IT department has his computer set up so he can remotely log in and stuff. Anyway, one day I E-Mailed him some VBA so I could copy/paste it from my E-Mail to the VBA module on his computer, and I ended up having to delete almost all of the leading spaces that I use for indenting. But that was a totally different task, unrelated to this one. I just mention it to compare to your issue with copy/paste.

Daniel - I did do a Compile/Repair on the file a few times today, just in case that was the issue. The fact that I can access it with the ADO method seems to indicate that this is more of a syntax issue than a database corruption issue, but I could be wrong.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Doug Steele
post Jun 5 2017, 08:36 PM
Post#8


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Dennis: At least some of the errors were that leading spaces weren't acting properly.

However, I also got at least one invalid reference-type error (sorry, didn't write the exact working down, but I believe it was saying that the OpenRecordset method was not appropriate for assigning to the rst variable) as well.

Just for giggles, try typing the code in from scratch.

--------------------
Go to the top of the page
 
LPurvis
post Jun 7 2017, 07:56 AM
Post#9


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Just skimming through...
Though I've no issue whatsoever with using ADO from an external source (it's often been my preference in such circumstances), I did notice the following:

>> I tried your code, but even with the Microsoft Access 14.0 Object Library checked, I still get the first DIM line highlighted, with the "user-defined type undefined" error.

You're declaring DAO objects. So you'd want the "Microsoft Office 14.0 Access database engine Object Library"
I know, it's just such a catchy name isn't it. Makes you wanna write a song about it.

Or you could use late binding instead... and no reference at all. :-)

Cheers

--------------------
Go to the top of the page
 
doctor9
post Jun 7 2017, 08:06 AM
Post#10


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Leigh,

Not sure why, but that isn't on the list of references I can choose from. Odd.

I did some Googling and apparently the file is named ACEDAO.DLL - when I do a file search, I see it appears in two places:

C:\Program Files (x86)\Microsoft Office\Office14
C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14

But when I use the Browse button in the References dialog box, the DLL files don't appear, even when I change the file type to All Files. VERY odd. I suspect the IT dept. has protected these folders in some way.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
LPurvis
post Jun 7 2017, 08:51 AM
Post#11


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Interesting. Is there no version of the ACE object library installed (i.e. other Office versions than 2010)?
e.g. "Microsoft Office 15.0 Access database engine Object Library"
or
"Microsoft Office 16.0 Access database engine Object Library"
etc.?

What if you try late binding.
CODE
    Dim objDAO As Object
    Dim objDB As Object
    Dim objRst As Object

    Set objDAO = CreateObject("DAO.DBEngine.120")
    Set objDB = objDAO.OpenDatabase("C:\Frontends\MTETesting_FE.accdb")
    Set objRst = objDB.OpenRecordset("SELECT * FROM qryPAVSampleLabels")
    'And then your recordset iteration

Does that run? I don't see how ACE can't be present if Access is installed.

Cheers

--------------------
Go to the top of the page
 
jleach
post Jun 7 2017, 08:53 AM
Post#12


UtterAccess Editor
Posts: 9,815
Joined: 7-December 09
From: Staten Island, NY, USA


I'm not sure how relevant this is, but I ran into a case where I realized that under certain circumstances at least, Office loads a complete virtual file system into place for when it runs. It's completely off the wall.

I think I was trying to hamfist an installer with an OCX control, but the thing didn't exist when I went in explorer, but it did if I went through references. A bunch of screwing around turned out that there's a virtual file system that gets loaded and used.

<insert mybrainisfullof...meme here/>

I'll dig around and see if I can find the email thread where I noticed this.

--------------------
Go to the top of the page
 
jleach
post Jun 7 2017, 08:56 AM
Post#13


UtterAccess Editor
Posts: 9,815
Joined: 7-December 09
From: Staten Island, NY, USA


Here:

-----

I don’t know what kind of black magic bs runs this show…

Windows 10 x64, Office 2016 x86.

I have an Access project with a reference to MSCOMCTL.OCX

But MSCOMCTL.OCX doesn’t exist on my system.

In the image below, the left dialog of course is references, and shows the path to the reference.
I click Browse in the references dialog, and get the middle window: an OpenFileDialog, which shows the file.
The right-most window is an Explorer window manually brought to SysWow64 – no file (or anywhere in the windows dir\subdirs, for that matter).

So I try to copy the file from the middle OFD to the desktop: error, this file doesn’t exist. Huh?!?!

This thing’s got me beat. I know Vista had a fun time with virtual directories and sometimes I’ll get tripped up by a shadow copy of a file somewhere, but this is entirely new to me. Time to step back and slap myself, see if I wake up!

Attached File  wat.jpg ( 180.09K )Number of downloads: 5


-----



Strangest thing I've ever seen.

Someone else found it here: C:\Program Files (x86)\Microsoft Office\root\VFS\SystemX86

"VFS" likely being Virtual File System.

Maybe it's a clue?

--------------------
Go to the top of the page
 
doctor9
post Jun 7 2017, 09:23 AM
Post#14


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Leigh,

>Is there no version of the ACE object library installed (i.e. other Office versions than 2010)?
>e.g. "Microsoft Office 15.0 Access database engine Object Library"
>or
>"Microsoft Office 16.0 Access database engine Object Library"

Nope. When I first saw a website referring to this library I assumed it was a typo because there was no "database engine" choice.

> What if you try late binding.

Hey! That worked! notworthy.gif

Would this be appropriate for the end of the subroutine?

CODE
    objRst.Close
    objDB.Close
    Set objRst = Nothing
    Set objDB = Nothing
    Set objDAO = Nothing


Do you "close" a DAO.DBEngine object? Or do you "quit" it?

Dennis

Jack: My IT department is a little nuts, but I wouldn't describe them as "off the wall". I'm pretty sure the installation of Office is pretty standard. However, they do have a tendency to "lock down" PC's so you can't install stuff on your own. I suspect their efforts on this front are to blame.

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
jleach
post Jun 7 2017, 09:28 AM
Post#15


UtterAccess Editor
Posts: 9,815
Joined: 7-December 09
From: Staten Island, NY, USA


This VFS thing was a vanilla install on my own system. Completely unexpected. Anyway, not sure it's your issue or not, but it rang a bell and I thought maybe it was worth mention.

Good luck...

--------------------
Go to the top of the page
 
doctor9
post Jun 7 2017, 09:35 AM
Post#16


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Aaaaaaand I'm a huge idiot.

I was looking for "Microsoft Access 14.0 Access database engine Object Library" (which doesn't exist), when I should have scrolled down further to find "Microsoft Office 14.0 Access database engine Object Library", which DOES.

<Bangs head on desk several times, sobbing gently> Dumb, dumb, DUMB...

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
DanielPineault
post Jun 7 2017, 10:07 AM
Post#17


UtterAccess VIP
Posts: 5,454
Joined: 30-June 11



woohoo.gif

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
doctor9
post Jun 7 2017, 04:27 PM
Post#18


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Okay, just so people in the future can see the syntax I ended up using, here you go:

CODE
'   NOTE: This code requires the Microsoft Office 14.0 Access database engine Object Library
'   instead of the Microsoft DAO 3.6 Object Library because we're working with .ACCDB files instead of .MDB files.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = DBEngine.OpenDatabase("C:\Frontends\MTETesting_FE.accdb")
            
'   Create a query to retrieve as many records as the user specified
    strSQL = "SELECT TOP 25 " & _
                " ""Proj# "" & [tblSampleLogin].[intProjectID] & "" ("" & [strSampleID] & "")"" AS Line1, " & _
                "tblSamples.strSampleDesc AS Line2, tblSamples.strSampleID " & _
                "FROM (tblSampleLogin " & _
                "INNER JOIN tblSamples ON tblSampleLogin.LoginID = tblSamples.intLoginID) " & _
                "INNER JOIN tblSamplesMAIN ON tblSamples.strSampleID = tblSamplesMAIN.strLoginSampleNo " & _
                "ORDER BY tblSampleLogin.dteDateReceived DESC;"
            
    Set rst = db.OpenRecordset(strSQL)
    
    
'   - Use the recordset data -


'   Cleanup
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing


I found that the "DAO." prefixes were necessary to prevent a syntax error when I tried to actually open the recordset. Otherwise, this is based on Doug's initial reply to my question. It's all my fault for not looking hard enough for the correct DLL Reference file.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Doug Steele
post Jun 7 2017, 08:08 PM
Post#19


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


In your defense, Dennis, I mentioned the wrong reference in my reply! blush.gif

As to needing to disambiguate using the "DAO." prefix, always a good idea to do that when you can. I'm wondering, though, did you still have the ADO reference in your list? And, if so, what was your complete list of references in order?

--------------------
Go to the top of the page
 
doctor9
post Jun 8 2017, 08:17 AM
Post#20


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Doug,

I didn't HAVE to remove the ActiveX reference for it to work, but I've removed it now that I won't be using that method any longer. Not that it really made a big difference in speed or anything.

I actually have quite a scary list of references, I suspect. I have the Excel object library so I can open an XML file in Excel, and play with the data. (Our company has a third party program that generates XML files when you want to export lists of details about samples we're testing, and the data is in very simple table format, so Excel handles it very neatly.) I've got an MS Forms reference as well, I think for an old macro I don't use any more. It says it's in use when I try to remove it, so I'm not sure what to do there. I also have the Office Object library... can't recall why I added that one, so I might just remove it and hope for the best.

Basically, I end up having to use Word for all sorts of things besides writing simple letters and user manuals, so there's a bunch of VBA code in my Normal file. I do -try- to add notations to the top of my macros that require a special Reference, but sometimes I'm in a bit of a hurry, and documentation is the first thing to get skipped when that happens, as you know.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 04:24 PM