Connecting to Excel File as DSN

[TotalImmortal]

Heres a quick little tutorial for how to connect to an Excel spreadsheet as your DB.

What is a DSN?
    A DSN, or Data Source Name, are data structures used to describe a connection to a database.

What is ODBC?
   ODBC is a standard software API used for database management. Think
of ODBC as a translator which takes generic SQL queries, and formats them
into a language that the DSN in question can understand.

Creating the DSN

1. Control Panel -> Administrative Tools -> Data Sources (ODBC)
a. [User|System|File] DSN
User DSN is if you are the ONLY user who will be using this DSN.
System DSN is if multiple users on the machine will be using the DSN.
File DSN, however, is not really a data source. It is a file that contains all the connection parameters used to connect directly to an ODBC driver.
b. Select the DSN tab which applies (I usually use System, but i dont worry about someone else using my DSN's ) and select 'Add'. Scroll to 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

c. Fill out fields appropriately. Data Source Name is the name you will reference in your program to connect. Details is just good to fill out accurately because, as all you stunning young programmers should know, documentation is key.
d. Select "Select Workbook"

Congrats! You have added an Excel spreadsheet as a DSN successfully... but who cares about that lets learn to use it, shall we?

Connecting to your Excel Spreadsheet

I will just throw down some code here, and put in some comments for ya. Should be pretty self explanitory.

'This is the actaul connection itsself.
Set oConn = CreateObject("ADODB.connection")
' "dsn=" simply lets VB know 1. that we are using a DSN and 2. the name.
'VB will know where to find the DSN at. You do not need "c:\...."
oConn.Open "dsn=auto-fill"
'For doing queries, the sheets are equivilant to the tables.
'Sheet1 is the default name for the 1st sheet in Excel. If your sheet is
'named "Names", your query will look like this:
' SELECT * FROM [Names$]
sSql = "Select * from [Sheet1$]"

Well that should be about it.... Feel free to tell me if I got anything wrong! Btw, the code is VBScript, not VB .net, so it might be a little off, but the idea is there pretty well.

Thanks for lookin!

 

[cjard]

Shouldnt you be posting that to VBScriptForums.com ? (We dont really use DSNs any more, and that code certainly isnt how we connect to databases, sorry)

 

[TotalImmortal]

Aye, alright then. Most of the projects that I am going to be on, my boss wanted in VB.net, so Ill keep this project off the forums ;-)