[mso] Re: Access and ADO
- From: "Dian Chapman" <dian@xxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Sun, 9 Nov 2003 11:02:13 -0600
>Ummmm... Dare I ask what's the diff between a DSN and DSN less connection
please?
Pretty simple, actually...DSN means you set up a data source name through
the control panel by creating a ODBC connection to it. PITA!
DSN-Less means you just use the dynamic server path statement and pass the
static link details to the DB location.
If you check out that link I provided...there's a more detailed/technical
answers.
I don't have time right now to review your code, completely...but I noticed
a couple quick things...
* I don't think the SETs should be within your path variable
* And rather than using the cursor and locking CONSTANTS, use the numbers!
If you use the constants, you have to make sure you have the correct
references set. I always end up getting errors due to that and lots of time
they're confusing errors. In fact...I think I they might have even been
permission errors???? You need to make sure the ADOVBS as an include file
when using for the web...but you need to set the proper references if it's
an app, like you're doing. Easier to just use the numbers....
...so rather than saying
oRS.Open "Select * from plot.log", oConn, _
adOpenStatic, adLockReadOnly, adCmdText
...try this way
oRS.Open "Select * from plot.log", oConn, ?, 1, 1
...where,
adOpenStatic = (sorry, don't know off hand, you can look it up...try
W3School)
adLockReadOnly = 1
adCmdText = 1 (or maybe even try 8 which is adCmdUnknown..let the
provider figure it out???)
Gotta run...for now...
Dian D. Chapman
Technical Consultant,
Microsoft MVP & Instructor
Free Tutorials: www.mousetrax.com/techtrax
Free Word Tips & Tricks eBook: www.mousetrax.com/books.html
Learn VBA the easy way, thru video! www.mousetrax.com/techcourses.html
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of Green
Sent: Sunday, November 09, 2003 7:44 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access and ADO
Hi Dian,
That's an awfully long answer!!! Thank you!
> To be honest with you Lisa...I'm not quite sure what it is you're
> doing.
I'm tring to use access as a GUI reporting tool for a comma delimited text
file with a .LOG extension.
> But I never used DAO
I'm trying not to as well :-)
I'm trying to not use DAO at all here. Mentioning it could have been a
mistake as it probably confuses things a bit.
> Was the PERMISSIONS error specific to permissions on your system?
I'm sorry but I have no idea. I suppose so because this is a standalone
comuter running XP home.
>It's much easier to create a DSN-less
> connection with
> ADO
Ummmm... Dare I ask what's the diff between a DSN and DSN less connection
please?
> Post the code you are using to open and query it...maybe I can see
> something within that that is causing an error.
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim slPath As String
Dim ofield As ADODB.Field
Dim slCS As String
slPath = Application.CurrentProject.Path Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset
slCS = "DefaultDir=" & slPath & ";"
slCS = "Provider=Microsoft.Jet" _
& ".OLEDB.4.0;Data Source=" & slPath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
oConn.ConnectionString = slCS
oConn.Open
oRS.Open "Select * from plot.log", oConn, _
adOpenStatic, adLockReadOnly, adCmdText
> And yes, a LOG file probably isn't recognized as a viable DB source?
> But a TXT file would be.
Is there any way of getting ADO to recognise .LOG files do you know?
In the extended properties maybe?
If I use a .TXT file instead the code will open the file.
I'm checking out your site and w3schools at the moment.
Thanks a lot for your help.
Regards
Lisa
> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
> Behalf Of Dian Chapman
> Sent: 08 November 2003 22:08
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] Re: Access and ADO
>
>
>
> To be honest with you Lisa...I'm not quite sure what it is you're
> doing.
>
> Okay...I understand that you want to use a CSV file as a DB
> representation.
> But I never used DAO...so I'm not sure what you needed to connect to
> it with DAO (I got into this just as DAO was going out the door, so I
> jumped on the ADO bandwagon to learn cos' it seemed easier to do).
>
> In DAO did you need to provide the code for the "provider"
> you'd be using?
> If so...what provider are you using??? I've only worked with Access
> and SQL DBs so I'd use the JET provider for Access DBs:
> "Provider=Microsoft.Jet.OLEDB.4.0;" or the SQL provider for those DBs
> (don't have that one handy...as I just happen to be writing ASP/ADO
> code right now to finalize my contest pages for TechTrax...using JET).
>
> Post the code you are using to open and query it...maybe I can see
> something within that that is causing an error.
>
> NOTE...the book *I* recommend to learn ADO is the first book I
> used...which made it all make sense: ADO for Dummies! You can find a
> link to it on my recommended books web site at
> www.mousetrax.com/books.html#ado.
>
> And this is a great learning source for web coding: (click the
> W3Schools) http://www.mousetrax.com/Resources.html#learning
>
> Was the PERMISSIONS error specific to permissions on your system?
>
> Did you create a DSN ODBC connection to the DB? That may have
> something to do with permissions? It's much easier to create a
> DSN-less connection with ADO...which is what I always use...so I've
> never bothered messing with the DSN ODBC setting in the control panel,
> but once...and realized I needed to find an easier way...hence
> DSN-less. See http://www.w3schools.com/ado/ado_connect.asp
>
> And yes, a LOG file probably isn't recognized as a viable DB source?
> But a TXT file would be.
>
> Well...yes, it SHOULD be updated as live. But I don't know for sure
> cos'
> I've never done anything this way with a log file. I've only used true
> DBs.
>
> Example...my TechTrax database on my web site. Yes, whenever someone
> new enters a subscription, I can instantly see the latest info, if I
> want, via my live connection to the web DB.
>
> OR...(to keep out of OT<g>)...say you put this into an Access DB and
> had it linked via ADO to a Word form. If, say, some other source had
> access to add data to the DB via some other means...at any time you
> could open your Word form and retrieve the latest data...depending on
> how you had it set up.
>
> You might want to also check out my ADO code in a Word <--> AccessDB
> sample I have on my site. Go to
> www.mousetrax.com/techpage.html#autoforms and note the # 5 article in
> the Please fill out this form series of article I have (near the
> bottom of that list). Click the Download to go to another page, I
> believe, and then download the sample. You can open the form in Word
> and hit
> Alt/F11 to view the connection code. If you read the article and set
> the path to: c:\ComputorCompanion and set the proper ADO reference in
> the VBE panel...the DB will work and you can add new data to the DB
> via the Word form.
>
> Hope SOMETHING in here helps! ;-)
>
> Dian D. Chapman
> Technical Consultant,
> Microsoft MVP & Instructor
>
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx
with the word "unsubscribe" (without the quotes) in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
