PDUK1 said:
In essence the answer to your question is Yes. Excel can work LIKE Access,
however you need to remember that Access is a relational database management
system and Excel is a spreadsheet environment or known as a flat database.
To make a pedantic point, an .mdb file (which Windows Explorer tells
me is a 'Microsoft Office Access Application') is just a flat file but
to be useful must be accessed using Jet data engine, which is
'relational'.
One may access the data in an .xls using the very same Jet data
engine, so Excel can work a *lot* like MS Access. However, an .xls may
also be accessed via the Excel UI of course, so many of the usual Jet
features, such as strict data typing, constraints, indexes etc, must
be forsaken or reproduced for the native Excel environment
If you know what you are doing e.g. you speak SQL DDL or can
manipulate the objects in the ADOX library, you can create a fully
working Jet .mdb database. Plus the .mdb file format is freely
distributable, AFAIK. So, not having the MS Access app is no excuse
<g>. Here's a quick example:
Sub Test2()
Dim Cat As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String
Dim strSql2 As String
' Amend the following constants to suit
Const PATH As String = "" & _
"C:\"
Const FILENAME_JET As String = "" & _
"New_Jet_DB.mdb"
' Do not amend following constants
Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>"
' Build connection string
strConJet = CONN_STRING_JET
strConJet = Replace(strConJet, "<PATH>", PATH)
strConJet = Replace(strConJet, "<FILENAME>", FILENAME_JET)
' Build sql statements
strSql1 = ""
strSql1 = strSql1 & "CREATE TABLE Pilots ("
strSql1 = strSql1 & " ID INTEGER NOT NULL PRIMARY KEY,"
strSql1 = strSql1 & " lname VARCHAR(35) NOT NULL,"
strSql1 = strSql1 & " fname VARCHAR(35) NOT NULL,"
strSql1 = strSql1 & " mname VARCHAR(35) NOT NULL"
strSql1 = strSql1 & " DEFAULT '{{NA}}'"
strSql1 = strSql1 & ");"
strSql2 = ""
strSql2 = strSql2 & "SELECT ID, lname, fname"
strSql2 = strSql2 & " FROM Pilots;"
' Create new Jet database
Set Cat = CreateObject("ADOX.Catalog")
Cat.CREATE strConJet
With Cat.ActiveConnection
' Create tables
.Execute strSql1
' Create some sample data
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (1, 'Livehulas', 'A')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (2, 'Katewudes', 'B')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (3, 'Hevitoxic', 'C')"
.Execute "INSERT INTO Pilots (ID, lname, fname)" & _
" VALUES (4, 'Norarules', 'D')"
' Query data
Set rs = .Execute(strSql2)
MsgBox rs.GetString
End With
End Sub
Jamie.
--
