How to Create an SQL Database with Excel with VBA

This basic tutorial shows how to create a MS SQL database with Microsoft Excel VBA. It also shows how to add a table and insert data into the table by creating a record. It's pretty basic so if you need any pointers feel free to comment.

---

Closed Caption:

hi guys this is a quick Microsoft Excel
database tutorial how to create and
recreate myself database using much of
the XL and then add to insert a table
with in that database and then we follow
on with inserting data into the table
that we just created in the database the
data were going to be passing into
database are these three values here
this is up for daddy and brought name
program d and price
ok now I don't look better on me which
will allow us to execute our our
function so I've created the code
already because it took awhile to view
and I wanted to soft really explain the
procedure tea and walking through the
code as opposed to have you watching me
typing it
ok so first thing we do is we hit a halt
f11 to get out and covered her up then
you can see all the code in the window
and I've created for you already get
down here at the bottom left of your
screen you got two little buttons i'm
going 10 full mode View and then one is
procedure view
ok so for watching obviously lists as
you can see here all the 4 module what
it says for module refuse to go view and
then look at your
project Explorer over here you go module
so all the code that you created that
much of you can view but in the for much
of you
ok used to add another module in here
you wouldn't see the code unless you've
selected the module and then you click
for watching this button on the left on
the other hand will allow you to show
just the individual subroutines within
your module and kindness option quite
clear or more clear to see their code in
this for you so I first thing we do is
we add our module and by right clicking
insert and so once about the margin
certainly start cutting my first module
first procedure I right in order was the
main and here we especially wanted to
Oh God basically wanted to create the
database and but first we have to check
the database exist that's pretty much
what this code does and if it exists
deletes it and then create a new
database if it doesn't exist and you
choose to not delete it and i'll just go
on a little trying to create the table
within the database insert data into the
table exists then we'll just go on
insert the data but we'll cover those
pics in a minute
so first thing we do is we don't mention
string which is we're going to store our
path and that's going to hold the path c
color matches ten test IMDb which is
we're going to call our database
ok so that string has been assigned to
this string variable okay we've created
a logic statement if ok and then we've
created our own custom function file
exists and we pass it the path which is
this okay so if the file exists then do
this okay and this is popup message box
and say to the user that takes place
already exists
do you want to delete it
yes on know this but here adds the
buttons to the database
this is the text that appears within the
database and then this is the just the
time sorry the message box and this is
the title of the message box okay and
when you click a button
yes or no then I integers returned and
the integer will relate to a vb yes or
maybe no okay we want except if it's yes
there is a yes we want to then delete a
file
ok and we then go on and we create the
database by calling our own custom
function its identity this delete files
a custom function which will go through
in a minute is this file exists which
will go through in a minute
this is create database will go through
in a minute okay so once you've created
a database we jump out already
statements and then we try and create
the table
ok the table doesn't exist we created
and if it does exist then we move on and
we don't create it we just gonna insert
data into an asset end of code
ok so anyway going back to up custom
pages file exists as a custom procedure
for in you look appear in your
procedures window look for file exists
select it and you can see the function
that we wrote written so we're saying
here it's a function so we wanted to
return value and we're going to return a
boolean which is a true or false
ok and ok so if passed the path along
with the filename as one string into our
function and this is kind of a system
procedure and which is a directory
listing on this parameter so it's going
to a directory listing on the prophecy
club Isis temp and it's going to be
looking for the file test2 IMDb and it's
saying basically is not eat this but he
says if not equal to empty
I they exist and they returned through
okay otherwise
it doesn't exist IE this day would
return would be a string would be true
and it returns false so it's basically
yes or no yes exist night doesn't exist
that's it and then we go back to our
main function think I got that we run as
far as to say if the file exists we want
to delete it and this will return true
for the fall existing rights we go back
to main file exists then pop up message
boxes we've already discussed delete
file level look at that
delete file very simple procedure we
friends again we parted far name sat
review pathname vb normal kill which is
essentially far
okay that's very straightforward go back
to the main create a base so we look at
create database we got so this is a cat
lock object placeholder this is the
connection pointed to the adobe connect
object and then this is just a string
which is going to help what we call it
the connection string and a connection
string is a database type and the source
path which is here
ok now these are compulsory also you can
change the path to if you like you can
change the names of these whatever you
like but the spring much day as you see
them on the screen here we set our
catalog object we say create a docs .
catalog object and the site or cat which
is except here an object
and then we create within that catalog
are connecting essentially what that
does is create the database in a
database in this directory
okay then it's nothing to the cap back
to the frame memory and there's nothing
to the connection string again firing at
memory when we're done with the create
database by this time the database
should be sitting in our hard drive okay
so you're back to main what we'll do at
this point we'll put a breakpoint in
here i'm going to run it and we'll see
what happens
click OK could not find 10 countdown to
get the book
ok say and I meant for Davis be calls a
little food part here so we need to put
an else create database
otherwise we're going to try and create
a table in the database that doesn't
exist
ok so as I said earlier check the
database exist if it does prompt the
user to delete it that do delete it will
go away and it will create a new
database if they don't delete it then it
will still be on the hard drive and
we'll hop over all of this code here and
go straight into creating the table
ok so let's kill that and let's run it
let's put a breakpoint here run ok its
run through what I'm looking are rare in
our template directory
there she blows look if I double-click
on that ok enable content you'll see
there's nothing in there about okay so
we'll close it down menu is that ok next
step is to create the table and I'll run
I'll run up another breakpoint him run
over there create table so now if we
look in our database we should have a
table I got our table up and out for a
name probability price each ok no data
in that amount
ok so close that down again go back to
our code click on in sep 12 and step
have another look at our database click
click on our table and there's data
ok now we run again obviously it's going
to come out and tell us database file
already exists
okay so do you want to delete it
yes or no listening know if we say no
and it's going to try and create the
table it will fell credit because the
table or exists but it will insert more
data so if we go back to our database
now look table you see we've got two
rows in l a-- ok and the more times you
run this
looks great . speed things up a little
bit okay click click no click now click
OK and go back to our database
ok just realized database when there was
at the screen you couldn't see it so
i'll bring into the screen and you can
sit and this is microsoft access by the
way
ok you can see we've added always
restore database
ok so very brief tonight and and this
just basically covers adding data
database so another tutorial we will go
through propagate through this in a bit
more detail actually and what's going on
and I've got to be quick but it is late
two in the morning here so i really want
to get this up until you as soon as
possible
any questions over the step King a text
and I get back text i will just buy