ODBC Connections - First Basic Query with Excel

In this session, Peter discusses the basics of creating queries in Excel using the PBS Accounts Payable Vendor file. Now don't be fooled, though it's a basic query, he goes into quite a bit of detail on what's going on "behind the curtain". It really sets the table to help you understand how the whole ODBC link works whether you're attaching to a SQL database, or one with an ODBC driver.

---

Closed Caption:

now as a first example and I'm you can
see here on my list of examples I'm just
basically going to walk through the
samples and comment on them as we get to
them the first sample here is is the
vendor file is a simple dinner file look
up and what it's doing is we're looking
at the the connection between the excel
spreadsheet in Excel spreadsheet and the
backend vendor database you can see it's
a it's a it's a regular spreadsheet and
i can show you that you know the fields
are coming in the way you expect so this
is a date field so i can add 90 days to
that date field and then copy it to the
various columns down here that sort of
thing that works
these are number field so I can take
this and come over here into an auto sum
and hit bang and enter and I've got a
total so we're dealing with a real
spreadsheet the difference here is that
all of these numbers are being pulled
out by what amounts to a sequel query
being issued to the PBS database and
that PBS databases responding by filling
the fields and giving us these numbers
back now the heart of the system comes
up here under data I I'm going two
thousand office 2007 it's very very
similar in office 2010 and somewhat
different in earlier versions of office
the features are all there but they're
just rearranged differently obviously
for those of you who haven't yet gone
getting used to the the famous ribbon
under 2007 up they've actually improved
this portion of the interface vastly and
all you need to do is pick up
connections and we have one connection
here and I go into properties and look
at the definition and that definition
tells me we're on getting the data it's
the sequel link which I will show you in
just a moment and then it shows me the
sequel statement and we'll come back to
that in a big way in a few minutes and
that's the actual sequel statement
that's being issued to the PBS back-end
to extract the data
there's a better not a better way a
different way of looking at that I can
actually look at the query in a kind of
a more graphical fashion so if i click
on edit query and I just come next pick
off the next here and look at the query
and Microsoft query this is the nature
of our query as set up by this this sub
function within Excel what's happening
here in and just a word of caution or
just a word to the wise not all excel
installations include the microsoft
query application it's something that's
free
it's only excel distribution discs but
sometimes people don't install them
this is a requirement for using either
sequel odbc simply because this is the
thing that formulates from the graphical
representation from your drag and drop
which will be looking at it from that it
actually formulates the sequel
statements and what i've done here is
i've requested their name number
city-state balance and first purchase
date I've requested those from this list
of fields that are in the vendor file
and so i can add others for instance
I've got a steady city state and zip I
could address one for example here and
then if i refresh i'm going to pick up
all my address once so this is really a
nice quick easy drag-and-drop kind of
environment i'm gonna get rid of this
because I don't really want it for my my
purposes but and then when I'm done with
the query what happens is it then
returns this data to Microsoft Excel and
we have this data that's been refreshed
or we can simply click refresh all up
here and it will up the data now this is
not a one-time thing when I save this
spreadsheet what i'm doing is i'm i'm
saving this as an xml file not annex
LS X so it's giving me some some
warnings here I'm but when I save this
file
I'm not only saving the current data
that you see right here including the
data I'd added i'm also saving all of
these parameters that we saw up here
that related to making the connection
and so what's going to happen is every
time I open the spreadsheet i can click
refresh and get the latest data so for a
real quick example I here i have a
pretty much a garbage vendor down here
if I bring that vendor up in PBS let me
just log into my sequel PBS equal bring
it up vendors sort by number down at the
end there
double-click this i'm going to just
change the name
the quick brown fox . save that now
that's written that back to sequel
I don't have to do anything else at this
point is written back to sequel so I
come back to here we see the original
junk in there we now click refresh all
once it's done
the quick brown fox so this tube is not
only established by this process it
maintained so that when i say this
database every time it comes up it has
the original data but i can also click
refresh all so that's that the kind of
take away i want to do from this first
simple example the one other thing i
want to talk about I want to talk about
uh we're a couple of the components are
and and how they work and then we'll be
on into some more examples so many
minutes and let me finish off with this
i think we can actually get rid of this
but I'll just minimize it
let's look at XD bc first in in here in
my course copy of version 12 0 and the
course what we have here is a folder
when we install the XD bc we end up with
a folder and basically this combination
of folders with files in them
corresponds to the data dictionary these
are the definitions of the fields in the
the case of sequel this is built into
the sequel schema so that there isn't a
special place but there is sequel out
there and so if we go out if we were to
go out and look at my sequel database we
would find out here let's go into here
program files and part of what i'm
trying to do here is also to take out
the mystery of out of this under
microsoft's equal ms 10ms equal here's
my stuff and you end and a variety of
backups and the data files themselves
and so on and so forth so we've got
these files jeans some of these were
updated this is the actual database
itself was updated today as well as we
did updates and so on and so forth so
this is where the sequel data is held
everybody knows where the PBS date is
held so the combination of the data
dictionaries and where the data is where
the sequel data is where the PBS data is
where the data dictionaries are for
sequel where the data dictionaries are
for the XD see the combination of those
two pieces is held in one spot which is
the data source name and that data
source name you can get at by running
settings control panel administrative
tools and odbc windows seven it slightly
differently arranged but it's the same
concept now for your most of your
clients you're just going to have one or
two obviously being in development here
and doing a lot of testing I've got a
lot of stuff out here
someone call it crap but it's my stuff
here is our sequel link and here is our
course data link we configured look at
the configuration for the XD bc it tells
us where are the dictionaries are and
actually it's a simple file that point
to where our date dictionaries are and
where our files are and by the way for
those of you who have not used this
before with any version any recent
version you want to change the maximum
columns to 512 it defaults to 256
because that used to be the excel
maximum but with the new version 7 excel
they've they've expanded the number of
columns and it turns out we have a
number of files things like the payroll
history that have substantially more
than 256 fields in them so that this
number needs to be opted to the next
limit up there which is 512 so that's
the XD set up the sequel setup because
it's kind of all stuck together with the
sequel database itself is rather more
simple and basically we look at what's
the name of our link and put in a
description and then this is the
instance name for those of you are
familiar with sequel this is the
instance name and uh there are
additional there are additional
parameters typically not touched in
behind us which we can go after but up
the default here is the PBS data and
that will have been set up when we
installed our sequel so there's really
not an awful lot out there but the
fundamental concept here that I want to
make come home is the fact that both the
XD bc and the sequel use this odbc
administrator this this connection
administrator to point to where the data
is so that you have a single name a
single name which tells the program all
the technical details from making the
connection it's like having the gps and
punching in favorites go home
you don't need to do anything after that
it sets everything up and director hole
that's that's the data source name now
just coming back to our spreadsheet we
were looking at the connection data
under data connections if we look at the
properties on the definition sheet of
the properties
here is that dataset name and this is
more the information because my second
point to the wise kind of point here is
if you doing development work whether
it's in access or Excel or crystal
almost inevitably the data source name
that you will be using on your machine
will be different from the data source
name on your client machine that's just
in the nature of it they're probably
going to call it PBS or PBS p12 or
something where you you like me you may
have a dozen or two dozen different
setups for different clients and so on
and so forth and you have to keep them
straight
well this should not be in any respect a
hindrance because for example here in
Excel lease when we take this particular
spreadsheet to the client
all we have to do is having set up the
deep the DSN at the client we come into
these connection properties and change
this thing that's it and we're cool
so the point here is within the cavity
at that you will have to change one two
parameters in these connection
properties and they're similar things
and access and crystal within that cavi
at then you can develop on your machine
and take these over and simply rename
the data source name and basically
you're away