Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Friday, March 29, 2024 
 
How to Connect to External Data Using ADO  
Description:  Welcome to the wonderful world of ADO. With SalesLogix v6, ADO is the new standard for accessing data. Whether you're accessing data from SalesLogix, or from another data source, ADO is the technology you'll use inside of a SalesLogix VBScript. This article will focus on the aspect of accessing external data using ADO. We'll look at three different examples using Access, Excel, and CSV files.

Category:  Architect How To Articles
Author:  Ryan Farley
Submitted:  11/27/2002
   
Stats: 
Article has been read 32965 times

Rating: - 4.9 out of 5 by 13 users
 

fiogf49gjkf0d
How to Connect to External Data Using ADO

Welcome to the wonderful world of ADO. With SalesLogix v6, ADO is the new standard for accessing data. Whether you're accessing data from SalesLogix, or from another data source, ADO is the technology you'll use inside of a SalesLogix VBScript. This article will focus on the aspect of accessing external data using ADO. We'll look at three different examples using Access, Excel, and CSV files.

The Magic is in the Connection String

One of the great things about ADO is that you work with all data, regardless of the source, in the same way. The difference is the connection string. The connection string tells ADO which provider to use and other details of where to find the data. So, whether you are accessing a SQL database, an Access database, an Excel spreadsheet, or a CSV file, you will work with the data in the exact same way. The provider translates the complex details specific to the data source into the interfaces implemented by ADO so you can access the data generically, regardless of where it comes from.

Connecting to Access

Lets take a look at an example of how to connect to an Access database. We'll use VBScript, but these same examples will work in pure VB as well.

First, the connection string. In this connection string we'll use the Jet 3.51 engine (although we could use Jet 4.0 instead)

Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Data\MyAccessData.mdb

Pretty simple. Let's look at how we would use that. Throughout this article, we'll use the same example of looping though the data to fill a SalesLogix listbox. You'll get the idea of how you could use it to read fields, etc from the data source.

Dim objConn
Dim objRS

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Data\MyAccessData.mdb"

    Set objRS = objConn.Execute("select * from customers")
    With objRS
        ListBox1.Items.Clear
        While Not (.EOF Or .BOF)
            ListBox1.Items.Add .Fields("CompanyName").Value & ""
            .MoveNext
        Wend
        .Close
    End With
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing

That was easy enough. To step though what we just did; we made our connection with a connection string using the Jet provider, opened a Recordset, looped through it to fill our listbox, then closed out the objects. Easy.

Connecting to an Excel Spreadsheet

To read from Excel, there will be little difference. Again, just a different connection string.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Book1.xls;Extended Properties=Excel 8.0;

This uses the Jet 4.0 provider, however, if we wanted to, we could also use the ODBC driver for Excel with a connection string that looked like this:

DBQ=C:\Data\Book1.xls;DRIVER={Microsoft Excel Driver (*.xls)};

Makes little difference, but we'll use the Jet provider. As a note, whenever you see a connection string with a driver specified and curly braces, you can assume that the connection is using an ODBC driver. Let's look at the same example as before, this time using Excel.

Dim objConn
Dim objRS

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Book1.xls;Extended Properties=Excel 8.0;"
    'or could use -> objConn.Open "DBQ=C:\Data\Book1.xls;DRIVER={Microsoft Excel Driver (*.xls)};"

    'With Excel & ADO, you work with the sheet as a table
    Set objRS = objConn.Execute("select * from [Sheet1$]")
    With objRS
        ListBox1.Items.Clear
        While Not (.EOF Or .BOF)
            ListBox1.Items.Add .Fields("CompanyName").Value & ""
            .MoveNext
        Wend
        .Close
    End With
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing

Look familiar? Only the connection string changed. You'll notice from the example above that you work with the spreadsheet as a database, and with the worksheets in the Excel file as the tables.

Connecting to a CSV File

For our last example, we'll look at using a CSV file as our data source. The connection string for CSV files will differ a little from what we used earlier. First, we will need to indicate in the connection string whether or not the CSV file has a header row. Second, we will be connecting to the directory that contains the CSV file, not the CSV file itself. Once we've connected to the directory, the CSV file will appear as a table in that directory. Let's look at the connection strings.

First, here is how the connection string will look if our CSV file does have a header row (In this example 'C:\Data' is a directory that contains my CSV file):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data;Extended Properties='Text;FMT=Delimited'

This is our connection string if our CSV file does not have a header row (In this example 'C:\Data' is a directory that contains my CSV file):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data;Extended Properties='text;HDR=NO;FMT=Delimited'

Now, when you connect, remember that you are connecting to the directory where the CSV file is located. Any CSV files in that directory would appear as tables. To select the rows from a CSV file, you simply select * from the CSV file. Here's the same example we did earlier to fill the listbox.

Dim objConn
Dim objRS

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data;Extended Properties='text;FMT=Delimited'"

    'The CSV file "mydata.csv" is our table
    Set objRS = objConn.Execute("select * from mydata.csv")
    With objRS
        ListBox1.Items.Clear
        While Not (.EOF Or .BOF)
            ListBox1.Items.Add .Fields("CompanyName").Value & ""
            .MoveNext
        Wend
        .Close
    End With
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing

Wasn't that easy? Reading CSV files as if they were the same as SQL tables?! Gotta love it.

Wrapping it up

This article may have only touched upon (and lightly at that) what you can do with connecting to external data from SalesLogix using ADO. You can see that beyond the connection string, there is very little, if any, that differs from data source to data source. Knowing what is possible with ADO, you can make your data access programming a breeze.

Until next time, happy coding.
-Ryan
 

About the Author

  Ryan Farley
(SalesLogix Business Partner)
Customer FX Corporation

fiogf49gjkf0d

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix since 2001 and believes in sharing with the community. He loves C#, Javascript, Python, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

View Ryan's SalesLogix Mobile Seveloper Series
View Ryan's SalesLogix SData Developer Series
View Ryan's Git for the SalesLogix Developer series



View online profile for Ryan Farley
 

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
 
Please log in to rate article.
 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
RJ Samp

slxdeveloper.com Forum Top 10 Poster!

ADO Connections
Posted: 11/27/2002 7:07:40 AM
fiogf49gjkf0d
Great Stuff......my only problem with this is I'm unclear how to read each record, and each column within the record.

Thanks Ryan!
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Reading each record...
Posted: 11/27/2002 7:44:03 AM
fiogf49gjkf0d
As far as reading each row, that what the loop is doing.

While Not (rs.EOF Or rs.BOF)
'do something with current row here
rs.MoveNext
Wend

Reading the fields is easy. The Recordset has a Fields collection. You can access fields from the collection by name or by index.

sVal = rs.Fields("myfield").value
-or-
sVal = rs.Fields(0).value

I usually append a blank string to the end to avoid having to check if it is null (so if null it will then be a blank string so I won't get errors if I try to use the value)

sVal = rs.Fields("myfield").value & ""

Another article is coming soon (almost done) that will show common ADO items and how to translate the v5 way of doing things (DBOpenSQL to ??? kinds of stuff) to the v6/ADO way.

-Ryan
 
Mike Pawlowski
 

Alternative method of reading fields in a record
Posted: 12/6/2002 5:02:05 PM
fiogf49gjkf0d
I have found that using the bang operator (!) in order to retrieve a field value by name is fast, easy, and for me anyway, fairly intuitive. For example:

sval = rs!FieldName

I have read that there may be some performance issues when using this method over using the fields collection (as in Ryan's examples). I have yet to run into a serious issue with it however.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

True Mike...
Posted: 12/6/2002 7:02:52 PM
fiogf49gjkf0d
Thanks for bringing that up. Since the Fields collection is the default indexed property, you can also access the values with the shorter syntax:

sVal = rs(0).Value

And, since the Value property is the default property of a Field object, you can make it even shorter, like this:

sVal = rs(0)

However, I always prefer to be explicit when accessing properties so I go for the full syntax. I feel it leaves less ambiguity for others that may read your code later and leaves less "to chance". Not only that, if you use a With block, you'll have to access it via the Fields collection explicitly anyway:

With rs
sVal = .Fields(0).Value
End With

-Ryan
 
mary lou stephens
 

ADO Most valuable with multiple platforms
Posted: 12/9/2002 1:38:02 PM
fiogf49gjkf0d
Our order system sits on AS/400 so when user makes a sale
ADO allows sales logix to make query to AS/400 file
then insert as/400 dollar data toSLX table.
If code is kept simple the same code can be used with pervasive, as/400, mssql, 4th demension....
 
Robert C. Levine
 

Very Nice
Posted: 2/18/2003 4:26:41 PM
fiogf49gjkf0d
Ryan,
Another good Article. And folks, this is not meant to be full definition of ADO. We have books and references for that purpose. I do wish, however, that SalesLogix's manuals included chapters on the use of such things as ADO within the context of Version 6 and VBScript.

Bob Levine
Advantageware, Inc.
 
Steve Vassallo
 

Updating SLX using ADo and creating TEF files....
Posted: 4/22/2003 8:02:54 AM
fiogf49gjkf0d
Is this an easy process and does it happen?

Thanks for any info
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Updating SLX using ADo and creating TEF files...
Posted: 4/26/2003 2:34:23 PM
fiogf49gjkf0d
As long as you use the SalesLogix OLEDB provider for performing your updates or inserts then the creation of TEF files happen automatically. The SalesLogix provider takes care of all of that for you. So, really, the only thing you have in your code other than straight ADO is a connection string to the SLX database via the provider. (See: Understanding the SalesLogix OLE DB Connection String)

If you are dealing with a version of SalesLogix prior to v6, then you cannot write to the database via ADO since v5 and prior does not have an OLEDB provider to use. You'd have to use the SalesLogix API (SlgxApi.DLL) to write to the database from external apps from older versions using methods such as slapi_DBExecuteSQL etc.

-Ryan
 
Patsy Trickel
 

Re: How to Connect to External Data Using ADO
Posted: 11/11/2004 1:54:03 PM
fiogf49gjkf0d
Ryan,
"Another article is coming soon (almost done) that will show common ADO items and how to translate the v5 way of doing things (DBOpenSQL to ??? kinds of stuff) to the v6/ADO way. "

Is this completed yet ? It would help with my conversion a lot.
Patsy
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to Connect to External Data Using ADO
Posted: 11/11/2004 2:16:36 PM
fiogf49gjkf0d
Patsy,

Wouldn't it be nice if I could get all the articles I've started at some point completed. Problem is that my interest of what I want to write about changes from time to time and if I didn't complete an article before then it usually doesn't get done.

With the 800 or so users registered on this site, I doubt I am the only one with knowledge to put an article together. I'd love to see articles submitted from others as well (hey, I still have to work for a living too) ;-)

-Ryan
 
Nick Hollis
 
slxdeveloper.com Forum Top 10 Poster!

Re: How to Connect to External Data Using ADO
Posted: 8/16/2005 8:44:24 AM
fiogf49gjkf0d
Thanks Ryan, this article was a real help. Just wondering are there many major differences with regard connections between ADO and ADO.NET?
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to Connect to External Data Using ADO
Posted: 8/16/2005 11:08:05 AM
fiogf49gjkf0d
Nick,

The concept is the same. The connection string is the key part in connecting to any data source. However the objects used a quite a bit different. You use DataReaders, DataAdapters, and DataSets, instead of Recordsets. While there are still connection and command objects as in classic ADO, the practice of how they are used will differ in some regards. ADO.NET uses primarily a disconnected model, as opposed to classic ADO which is almost entirely connected.

That said, as far as connecting to different data sources, the concepts are the same, more or less, as described in this article.

-Ryan
 
Andrew Pettit
 

Re: How to Connect to External Data Using ADO
Posted: 9/18/2006 6:33:57 PM
fiogf49gjkf0d
Ryan,

I am having trouble getting a Recordset to return from a Stored Procedure in a MAS 500 database. All the SP does is return one row of data that I want to strip out and place into some text controls on a SalesLogix tab. The MAS 500 connection works fine and returns a State of 1 or Open. The SP also executes without any errors, but the RS never Opens and always retruns a State of 0 or closed. I also tired using Output parameters and they came back blank as well. If I try and pull something else from the MAS 500 database like a table the RS works fine and returns the results, but nothing when using the SP. If I run the SP using Query Analyzer connected to the MAS 500 database as SYSDBA user it also works fine from there as well and returns the desired results.

Does Anyone know what this could be?

Thanks
 
Andrew Pettit
 

Re: How to Connect to External Data Using ADO
Posted: 9/18/2006 6:34:00 PM
fiogf49gjkf0d
Ryan,

I am having trouble getting a Recordset to return from a Stored Procedure in a MAS 500 database. All the SP does is return one row of data that I want to strip out and place into some text controls on a SalesLogix tab. The MAS 500 connection works fine and returns a State of 1 or Open. The SP also executes without any errors, but the RS never Opens and always retruns a State of 0 or closed. I also tired using Output parameters and they came back blank as well. If I try and pull something else from the MAS 500 database like a table the RS works fine and returns the results, but nothing when using the SP. If I run the SP using Query Analyzer connected to the MAS 500 database as SYSDBA user it also works fine from there as well and returns the desired results.

Does Anyone know what this could be?

Thanks
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to Connect to External Data Using ADO
Posted: 9/18/2006 7:14:14 PM
fiogf49gjkf0d
Hi Andrew,

Move this question into the forums, I'll post an answer there (this could get a bit involved and I don't want to clutter up the comments under this article too much and have your question get lost for others searching for it)

Thanks,
-Ryan
 
 

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 3/29/2024 6:21:42 AM