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    
 
   
Doug Steele
post Jun 8 2017, 05:58 PM
Post#21


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


I wouldn't expect it to make a noticeable difference in speed. However, if the ADO reference was higher in the list than the Office database engine one, any reference to Recordset would automatically become an ADO Recordset without the qualification.

--------------------
Go to the top of the page
 
JonSmith
post Jun 8 2017, 06:11 PM
Post#22



Posts: 3,160
Joined: 19-October 10



I'll need to check this more thoroughly later but I think you guys are showing your MSAccess bias tongue.gif

I am pretty sure you dont need any Access references. Just an way to open a recordset.
You then just need a normal connection and can get the data.

I'll double check that I think there is a simpler way tomorrow and get back to you.


That being said Dennis, whats the wider context of this? Getting data into Word? Its kinda messy unless you know a few tricks!!
Go to the top of the page
 
JonSmith
post Jun 9 2017, 04:02 AM
Post#23



Posts: 3,160
Joined: 19-October 10



Ok, tested. This works without MS Access even installed and no opening the databases or creating workspaces etc etc. Its based on your original code but paths are different so I was able to test.
It connects to a .accdb and reads data from a query in that database. I can't remember the specifics of the 0's and 1's I used in the recordset open method as they were left over from late binding but it should be opening a read only forward only recordset. I am not sure if the method you landed on will lock your db to other users?

CODE
''"Microsoft ActiveX Data Objects 2.0 Library".'
    Dim rsCon As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    ' Connect to Database
    Set rsCon = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    rsCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Q:\CPM\GRIF\Data\Users\Data.accdb;Persist Security Info=False"
    rst.Open "SELECT * FROM qryAllClientsMonths", rsCon, 0, 1, 1
    
    Do Until rst.EOF
        Debug.Print rst.Fields(0) & ": " & rst.Fields(1)
        rst.MoveNext
    Loop

    rst.Close
    rsCon.Close
Go to the top of the page
 
Doug Steele
post Jun 9 2017, 06:20 PM
Post#24


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


Jon: If you look back to one of Dennis's posted from June 5th, you'll see he said

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

--------------------
Go to the top of the page
 
JonSmith
post Jun 12 2017, 05:23 AM
Post#25



Posts: 3,160
Joined: 19-October 10



Hi Doug,

I am aware. If you notice I was suggesting that there was a simpler way to do what Dennis has trying to do without "Opening" the database or creating Workspaces etc etc.
I think that if the code posted doing that was used it could cause locking issues and wouldn't work for any user who didn't have MS Access, this is perhaps not a concern but since this was about Dennis not being sure why his code wasn't working or what reference was doing what it makes a huge amount of sense to me to show him the simplest way to connect with the simplest references.

JS
Go to the top of the page
 
doctor9
post Jun 12 2017, 08:12 AM
Post#26


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


Jon,

As I posted earlier, the problem was that I had not scrolled down far enough to find the correct Reference file to check. Once I did that, I was able to move on with my life. See post #18.

Thanks,

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
 
JonSmith
post Jun 12 2017, 09:14 AM
Post#27



Posts: 3,160
Joined: 19-October 10



Ok, fine. I am trying to just raise that the following in the code you used may have issues and there is an easier and simpler way that doesn't risk locking the data.
If you don't mind and are happy to continue with code that may lock your database from other users then cool, no problem, but in the interest of other people reading this topic at a later date I think its important to add my code to show the alternate way using ADO.
Go to the top of the page
 
doctor9
post Jun 12 2017, 09:53 AM
Post#28


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


Jon,

I'm not using ADO, I'm using DAO. Please see my post #18 in this discussion. I think you missed that one.

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 13 2017, 03:02 AM
Post#29


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


Hi

>> there was a simpler way to do what Dennis has trying to do without "Opening" the database or creating Workspaces etc etc.
I feel there's been a misunderstanding here, which I'll address in a moment...

>> I think that if the code posted doing that was used it could cause locking issues and wouldn't work for any user who didn't have MS Access
Yes, that's the misunderstanding as I see it. It seems to be that you've felt that the DAO code that's been in use throughout this thread is actually code treating Access as an Automation server.
It's just DAO code. Access' being installed or not isn't a consideration. For the code to successfully run it has analogous requirements to the ADO alternative.
Opening a database isn't a physical action. It would be equivalent to the VBA code used all the time from within an Access procedure.
Set db = CurrentDb
The difference there is that there's already an existing, convenient, DAO database object to be returned. In external applications, you have to create the first.
The correlation would be more reasonably between DAO's database object and ADO's connection object. (As opposed to an actual Access instance. There's an argument to be made that an ADO connection lies somewhere between a Workspace and a Database in concept in DAO.)
In both, you must establish the parent object from which you're going to query or update. An Access application isn't involved at all. Nor is Access required to be installed. (Though, if it is, then you naturally will have both DAO and ADO ACE libraries at your disposal.)

Record locking is no more likely with one than the other. The data actions you subsequently perform would determine that (though IME, ADO is more likely to leave a careless excusive lock on the database than DAO operations.)
(Even if Access itself was launched, record locking wouldn't be more likely. Or did you intend to talk about file locking.)

>> but in the interest of other people reading this topic at a later date I think its important to add my code to show the alternate way using ADO.
As Doug mentioned, ADO was first mentioned as an alternative much earlier in this thread. (Dennis had found the possibility of it in a link in his initial post.)
But it was primarily being explored as the DAO code wasn't using the correct reference to access an ACE database. (ACCDB)
Just to be clear for future readers, it's not that you need to take from this that a preferential method has been determined. (I've always been a fan of ADO but only use it from Access against a Jet/ACE source for very specific functionality requirements. But from other applications, for example to get a recordset to use CopyFromRecordset in Excel, I think that's fair game too.)
However DAO (also now known as "Microsoft Office 16.0 Access database engine Object Library") remains the most common and, as a rule, lower overhead means of accessing MDB and ACCDB data sources.

Cheers

--------------------
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 03:10 AM