Access and OLE DB
C3
it. In Access' Open... dialogue box there is no option for OLE DB.
Can anyone tell me how I can open a database using OLE DB in Access? And if
this isn't possible, can anyone suggest a (preferably free) program that
lets you connect to a database and view the data (using OLE DB).
thanks
Pink Panther
with OLEDB and thence the database.
AFAIK you can't just "open a database using OLEDB" and many types of
databases can't be opened up via a open file dialog - ie database servers.
To get to the data you need to use another method - either OLEDB or ODBC.
Essentially OLEDB is a low level database access interface, and to make it
easier to use, you use it through ADO - programming OLEDB is an advanced
topic I would say (for me anyhow).
ODBC does the same thing in essence, although MS want to eventually replace
it with OLEDB.
Be more specific about where the data is, what you need it for (ie you want
to permenantly link it to your database etc) and your understanding of data
access methods.
So in short - you can't (until someone here tells me otherwise that is!)
HTH,
Peter
"C3" <som...@microsoft.com> wrote in
news:3e446124$0$8817$afc3...@news.optusnet.com.au:
Albert D. Kallal
better OLEdb is better then good old fashioned ODBC, then perhaps I will be
sold on the technology.
For the low, or small number of users (50 to 200 users), most don't really
notice any difference between OLEdb,and ODBC anyway.
So, mostly we just use ODBC. However, when you use ADO connection in place
of a ODBC connection to your SQL-server database, you are using OLEdb.
Further, you can force your application to be 100% OLEdb native client to
SQL-server. If you create a ADP project in ms-access, then you are in fact
using a 100% OLEdb.
However, as mentioned, I would not really worry about this as I have never
meet anyone who noticed the difference in performance between OLEdb and
ODBC.
--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com
http://www.attcanada.net/~kallal.msn
Lyle Fairfield
news:A4u1a.224264$sV3.7...@news3.calgary.shaw.ca:
> Well, my answer is that when someone comes along and shows me how much
> better OLEdb is better then good old fashioned ODBC, then perhaps I will
> be sold on the technology.
>
> For the low, or small number of users (50 to 200 users), most don't
> really notice any difference between OLEdb,and ODBC anyway.
>
> So, mostly we just use ODBC. However, when you use ADO connection in
> place of a ODBC connection to your SQL-server database, you are using
> OLEdb.
>
> Further, you can force your application to be 100% OLEdb native client
> to SQL-server. If you create a ADP project in ms-access, then you are in
> fact using a 100% OLEdb.
>
> However, as mentioned, I would not really worry about this as I have
> never meet anyone who noticed the difference in performance between
> OLEdb and ODBC.
I can remember when this group was progressive, enthusiastic,
forward-looking. Now it's become so reactionary, conservative and just
plain stupid, that it's almost worthless to anyone other than beginners.
There's a core of regulars who are like old men on the porch of the
general store sitting around and extolling the virtues of Detroit Iron.
"Well, Zeke, what damn foolishness is them bastards in Redmond up to now?"
Zatttttttt goes the tobacco spit ...
"I don't know what they'll think of next, Bubba, but I'm agin it, ... pure
and simple! Why I minds the time ... "
--
Lyle
Michael (michka) Kaplan
> I can remember when this group was progressive, enthusiastic,
> forward-looking. Now it's become so reactionary, conservative and
just
> plain stupid, that it's almost worthless to anyone other than
beginners.
> There's a core of regulars who are like old men on the porch of the
> general store sitting around and extolling the virtues of Detroit
Iron.
>
> "Well, Zeke, what damn foolishness is them bastards in Redmond up to
now?"
>
> Zatttttttt goes the tobacco spit ...
>
> "I don't know what they'll think of next, Bubba, but I'm agin it,
... pure
> and simple! Why I minds the time ... "
MichKa: "Objection, your honor! Argumentative, and assumes facts not
in evidence!"
Judge: "Aren't there facts here?"
MichKa: "I can make an offer of proof that the notion of a "Jet-less"
environnment is really not possible in an Access app."
Judge: "Ok, I'll withhold my ruling on the objection until after I
hear that offer of proof."
MichKa: "Thank you, your honor. You see, in the MDB case you obviously
have Jet loaded. But in the ADP case -- if you use wizards, then Jet
gets loaded. If you import or export anything via the wizard, then Jet
gets loaded. If you use any of the DoCmd.Transfer* methods, then Jet
gets loaded. And if you use any form or report, then Jet gets loaded
(for the Jet expression service). Now, it is possible to postulate an
Access application with no forms or reports that never imports or
exports data, but aren't we dealing with fantasy at this point? And if
Jet is always going to be loaded then isn't the notion of "saving
resources" by using a supposed-Jetless application also fantasy?"
Judge: "Yes, I see your point. Objection sustained. The jury will
disregard Mr. Fairfield's attack on the people who seem to understand
the situation a bit better than he realizes."
--
MichKa
This posting is provided "AS IS" with
no warranties, and confers no rights.
Lyle Fairfield
in news:3e46...@news.microsoft.com:
Hmmm ... I'm not sure what "Jet Gets Loaded" has to do with what I said. I
could postulate, You Honour, that Michka "is loaded", but I think we
implcitly agreed not to attack each other any more. So I won't.
What he says is fascinating. Could I ask him to explain "Gets Loaded". I'm
sure he doesn't mean ... there is a reference set to DAO ... since I have
a bunch ADPs with reports and forms which have no such reference (although
I never use any of that other nasty stuff).
So, I'm assuming Access loads in the JET objects to complete its tasks,
but doesn't confess to having done so by showing the reference. Is this
so?
Well, as I said, it's fascinating ... But what does this have to do with
tobacco juice?
--
Lyle
C3
performance. The reason I'm having trouble with ODBC is because it's
complaining about invalid characters in the field names. This, as you can
imagine, is not under my control.
regards,
C3
"Albert D. Kallal" <kal...@msn.com> wrote in message
news:A4u1a.224264$sV3.7...@news3.calgary.shaw.ca...
Albert D. Kallal
load.
You can also use a dns-less connection in DAO, and again you can accomplish
this without jet loading. However, one does need the DAO reference to do
this!!
However, I not trying to shoot OLEdb here. I simply saying that is it is not
going to ruin dinner if you don't use, or even have OLEdb available..
It is not a make, or break issue in most cases.
Most companies are now providing a OLEdb component anyway.
Should one use OLEdb? Sure...
Can we put JET to rest? Gee, it is just not a problem either way right now
to me.
I cling only to a something that will get the job done. It is not question
of old, or new.
However, the presence of JET in a ADP project is certainly news to most of
us!
Michael (michka) Kaplan
> However, the presence of JET in a ADP project is certainly news to
most of
> us!
Which is why I chose to mention it.
Albert D. Kallal
Each database vendor now does usually make a OLEdb component. You have to
install that. Thus, you get the OLEdb object from the provider. You will
then need the correct ado connection string to use that provider.
Thus, you have to go the vendor of the database you are connecting to, and
check with them.
Moving to a OLEdb provider may not necessarily solve the field character(s)
problem.
Are you doing a update, or building your SQL. Can you do a select * from the
database to see what fields are returned?
Or, are you dealing with the use of wild card characters like % (SQL server)
and * for ms-access/jet?
Perhaps you might try a pass through query. Even better is to ignore JET alt
altogether.
You also did not mention what version of ms-access you are dealing with.
However you can give ODBCdirect a try.
Try:
Dim strCon As String
Dim rstRecords As dao.Recordset
Dim wrk1 As Workspace
Dim MyCon As Connection
Set wrk1 = DBEngine.CreateWorkspace("testing", "", "", dbUseODBC)
strCon = "ODBC;driver={MySQL};DSN=;" _
& "SERVER=localhost;" _
& "DATABASE=test;" _
& "UID=;PWD=;OPTION=3;"
Set MyCon = wrk1.OpenConnection("mycon", dbDriverNoPrompt, False, strCon)
Set rstRecords = MyCon.OpenRecordset("select * from myaddress")
The above is a DSN less connection I have used to MySql. While the above is
DAO, it does not load, or use JET. It also means that joins etc are NOT
updateable quires as they can be when using JET.
Michael (michka) Kaplan
> Hmmm ... I'm not sure what "Jet Gets Loaded" has to do with what I
said. I
> could postulate, You Honour, that Michka "is loaded", but I think we
> implcitly agreed not to attack each other any more. So I won't.
Too late, you did. Sigh....
> What he says is fascinating. Could I ask him to explain "Gets
Loaded". I'm
> sure he doesn't mean ... there is a reference set to DAO ... since I
have
> a bunch ADPs with reports and forms which have no such reference
(although
> I never use any of that other nasty stuff).
>
> So, I'm assuming Access loads in the JET objects to complete its
tasks,
> but doesn't confess to having done so by showing the reference. Is
this
> so?
Yes, that is so. And thus many of the benefits of a "Jet-less" format
are supplanted.
> Well, as I said, it's fascinating ... But what does this have to do
with
> tobacco juice?
Nothing. But your little tirade against the "old guard" which
basically called a lot of people ignorant ignored the fundamental fact
that Jet is used in your "Jet-less" scenario of an ADP.
Do me a favor -- follow the implicit agreement and do not imply anyone
is ignorant when you post.
Lyle Fairfield
So, what are the names of the files from which "Jet Gets Loaded" when an
Access ADP opens a form or report?
--
Lyle
David W. Fenton
wrote in <3e46d2ef$1...@news.microsoft.com>:
>"Lyle Fairfield" <lyle...@yahoo.com> wrote...
>> What he says is fascinating. Could I ask him to explain "Gets
>Loaded". I'm
>> sure he doesn't mean ... there is a reference set to DAO ...
>> since I
>have
>> a bunch ADPs with reports and forms which have no such reference
>(although
>> I never use any of that other nasty stuff).
>>
>> So, I'm assuming Access loads in the JET objects to complete its
>tasks,
>> but doesn't confess to having done so by showing the reference.
>> Is
>this
>> so?
>
>Yes, that is so. And thus many of the benefits of a "Jet-less"
>format are supplanted.
More to the point, Lyle is confusing DAO with Jet. An ADP is
DAO-less, but not Jet-less.
>> Well, as I said, it's fascinating ... But what does this have to
>> do
>with
>> tobacco juice?
>
>Nothing. But your little tirade against the "old guard" which
>basically called a lot of people ignorant ignored the fundamental
>fact that Jet is used in your "Jet-less" scenario of an ADP.
I never understood what was so supposed to be so obviously superior
about a "Jet-less" scenario in the first place, unless you're one
of the morons who doesn't understand what a great db engine Jet
happens to be and how to use it appropriately.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Dimitri Furman
news:Xns931DCBA962F9...@216.221.81.119:
> So, what are the names of the files from which "Jet Gets Loaded" when an
> Access ADP opens a form or report?
>
According to Process Explorer from Sysinternals, both mswstr10.dll
(Microsoft Jet Sort Library) and msjtes40.dll (Microsoft Jet Expression
Service) are loaded by Access as soon as you open an ADP. No other
libraries seem to be loaded when you open a form in an ADP.
I'm looking forward to the discussion as to whether or not this constitutes
"loading Jet".
--
(remove a 9 to reply by email)
Lyle Fairfield
24.168.128.74:
> mic...@spamless.trigeminal.nospamcom (Michael (michka) Kaplan)
> wrote in <3e46d2ef$1...@news.microsoft.com>:
>>Nothing. But your little tirade against the "old guard" which
>>basically called a lot of people ignorant ignored the fundamental
>>fact that Jet is used in your "Jet-less" scenario of an ADP.
>
> I never understood what was so supposed to be so obviously superior
> about a "Jet-less" scenario in the first place, unless you're one
> of the morons who doesn't understand what a great db engine Jet
> happens to be and how to use it appropriately.
So ...
If Michka is a Moron Then
A Jet-Less Scenario is Superior
End If
Well, I don't think Michka is a moron.
So now I have to ask
Is a Jet-Less Scenario Superior If And Only If Michael Is A Moron?
Jet-Less Scenario IS Superior <=> Michael Is A Moron
I can see that your logic is flawless and since you seem to have quite a bit
of insight in this matter could you explain what Jet-Less has to do with my
comment?
'I can remember when this group was progressive, enthusiastic,
forward-looking. Now it's become so reactionary, conservative and just
plain stupid, that it's almost worthless to anyone other than beginners.
There's a core of regulars who are like old men on the porch of the
general store sitting around and extolling the virtues of Detroit Iron.
"Well, Zeke, what damn foolishness is them bastards in Redmond up to now?"
Zatttttttt goes the tobacco spit ...
"I don't know what they'll think of next, Bubba, but I'm agin it, ... pure
and simple! Why I minds the time ... "'
BTW ... your count is up 27 from 411 to 438 in less than two months.
Congratulations!
ZZZZZZZZZZAAAAAAAAAAATTTTTTTTTTTTTT!
--
Lyle
Lyle Fairfield
news:Xns931ECE4801B...@199.184.165.241:
Well, there won't be a lot of discussion from me. I note that if you "de"
register msjtes40.dll, an XP ADP form crashes the whole application on the
first save.
So I guess I won't do that.
But I don't find this reliance so "fascinating" as what I had assumed from
"loading jet", ie that the whole Jet Object model was loaded. I think we
know from the progression of Windows that MS sometimes purges older
technologies over the course of several versions of a program.
And what "Jetless" had to do with my original comment is still shrouded in
mystery for me.
--
Lyle
rkc
> > about a "Jet-less" scenario in the first place, unless you're one
> > of the morons who doesn't understand what a great db engine Jet
> > happens to be and how to use it appropriately.
By that standard, I'm estimating that makes 99%+ of the world's population
morons.
David W. Fenton
<Xns931EDF63965C0...@216.221.81.119>:
[nothing worth reading]
This is why you're in my killfile, Lyle.
Please do everyone a favor and put me in yours.
David W. Fenton
<gI_1a.33444$Xr1.7...@twister.nyroc.rr.com>:
Well, I don't hold responsible those who aren't in the business.
Michael (michka) Kaplan
was the notion that by not load ing Jet, somehow one would be able to
have better working set and better performance. The fact that the Jet
expression service and the "Unicorn" Jet DLLs and also the fact that
the MDAC "stack" is bigger than anything that Jet loads was not taken
into account.
If you do not see working set as factor in app performance then I
guess we can end the conversation right here as were are not truly
communicating.
--
MichKa [MS]
This posting is provided "AS IS" with
no warranties, and confers no rights.
"Lyle Fairfield" <lyle...@yahoo.com> wrote in message
news:Xns931EE0A9FA1B9...@216.221.81.119...
Michael (michka) Kaplan
> So, what are the names of the files from which "Jet Gets Loaded"
when an
> Access ADP opens a form or report?
The Unicorn Jet dlls (loaded on boot) given by someone else in this
thread, the Jet expression service (delay loaded but will show up soon
after boot or when you first open a form or report).
Not sure why the names matter here.
--
MichKa [MS]
Michael (michka) Kaplan
You make promises to follow the implicit "no insult" thing yet
continually do things that are insulting. Could you please stop that?
--
MichKa [MS]
Michael (michka) Kaplan
--
MichKa [MS]
This posting is provided "AS IS" with
no warranties, and confers no rights.
"Dimitri Furman" <dfu...@cloud99.net> wrote in message
news:Xns931ECE4801B...@199.184.165.241...
Lyle Fairfield
> Lyle,
>
> You make promises to follow the implicit "no insult" thing yet
> continually do things that are insulting. Could you please stop that?
I am sorry you felt that was insulting. In my mind, it was all about David,
but I suppose I should have found another way than to use your nick.
--
Lyle