How do you store data in Excel as a database?

[Guest]

How do you store data in Excel as a database?
Can you search this info like in Access?
If so how do you program it or where can I go to learn to program it?
THanks
blaxjax

 

[Guest]

Excel is not a database. While you can do things to mimic some database
functionallity with things like Vlookup it simply is a poor substitute. Your
question is like asking how to pound in a screw with a hammer. While you can
pound in a screw with a hammer it really is not the right way to do it...

 

[Guest]

To add to Jim's comments, Excel is a number crunching data manipulater. It
is an excellent accounting or analysis tool. It has functionality to produce
charts to illustrate analytical data and mathematical results. Basically, to
use it as an inert data base would be under employing the power of Excel.

 

[Guest]

JLGWhiz,

But I use excel as my small data base but slow in process, it's ok for me
but think not the correct choice for great data base, is that right?

What's the correct stucture of a data base?

 

[Bill Renaud]

For a simple flat file (single table), you can create a "list" on a
single worksheet in Excel, then use the Database and Lookup & Reference
worksheet functions. This is OK for a few dozen records.

Basically, you put column labels (Headers) in row 1, then put the data
on each row underneath.

See the "Guidelines for creating a list on a worksheet" topic in Excel
Help.

 

[Guest]

Bill,

Right Bill, some of my data stored in Excel Table like Access.
For the faster way I use *.Txt files to store data but still having problem
in security that txt files have no security.

 

[Guest]

Halim, the key word in my comments is inert, meaning that the data does
nothing but reside on the file. That is my concept of data base. Like your
text files that do nothing but store the data. To use the data, you must
extract it and use another tool to calculate, arrange, and graphically
display the data.

Excel has limited use as a data base file, as Jim pointed out. It was not
designed for use as a data base, but it was designed for use with data from
other data bases to do analytical processes and accounting functions. While
Excel allows the user to set up formatted records, and appears to be the same
as a data base file, it is not. Data base files have specific data fields
within a data record. The data record is of specific length and each record
has the same specifications. While Excel records might look the same,
technically speaking they are not.

 

[gimme_this_gimme_that]

See if this works for you. Works for me on XL 2003.

Google ADO.

I don't know how you'd add a where statement. Anyone?

Public Sub Query()
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range


ActiveWorkbook.Save
sFile = ActiveWorkbook.FullName


' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"


' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1


If Not oRS.EOF Then
Worksheets("Sheet2").Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If


' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"
' Query based on a sheet-level range name.
sSQL = "SELECT * FROM [Sheet1$MyRange]"
' Query based on a specific range address.
sSQL = "SELECT * FROM [Sheet1$A10:F16]"


End Sub

 

[gimme_this_gimme_that]

I don't know how you'd add a where statement. Anyone?

I discovered this ...

To do a selective query, you should use a named range with a header
row that
is the column names, and then query the column name

SELECT * FROM range_name WHERE Name = 'Joe' AND CtyCode = 'GB';

Also you can filter the data from the Recordset.