...
1. True/False: MS Access can be used to meticulously construct MyBase.mdb
initially
True. That is one of its key features. However, I thought the point
was you don't have MS Access. The same is true for ADO. The code I
posted creates an .mdb file i.e.
' Create new Jet database
Set Cat = CreateObject("ADOX.Catalog")
Cat.CREATE strConJet
But what you mean by 'myBase'? Just change the name in the sample
connection string. And what do you mean by meticulously in this
context? You can use DDL in ADO to create and maintain tables,
constraints, indexes, user accounts, etc. There are some things that
can be achieved using ADO that can't be done in the MS Access UI e.g.
specifying DEFAULT in a CREATE TABLE statement, the CHECK..SELECT
style of constraint, etc. For further details see the following
articles:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp
2. You have a sample code to demonstrate syntax for Updating and Deleting
records
Here's some very similar code that includes UPDATE and DELETE syntax:
Option Explicit
Sub Test2()
Dim Cat As Object
Dim Con As Object
Dim rs As Object
Dim strConJet As String
Dim strSql1 As String
Dim strSql2 As String
Dim strSql3 As String
Dim oTarget As Excel.Range
Dim lngCounter As Long
' Amend the following constants to suit
Const FILENAME_JET As String = "" & _
"New_Jet_DB88.mdb"
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>", _
ThisWorkbook.Path & Excel.Application.PathSeparator)
strConJet = Replace(strConJet, "<FILENAME>", FILENAME_JET)
' Build sql statements
strSql1 = ""
strSql1 = strSql1 & "CREATE TABLE CommsUsers ("
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 & "CREATE TABLE Phones ("
strSql2 = strSql2 & " ID INTEGER NOT NULL"
strSql2 = strSql2 & " REFERENCES CommsUsers (ID),"
strSql2 = strSql2 & " Phone VARCHAR(15) NOT NULL"
strSql2 = strSql2 & ");"
strSql3 = ""
strSql3 = strSql3 & "SELECT cm.ID, cm.lname,"
strSql3 = strSql3 & " cm.fname, ph.Phone"
strSql3 = strSql3 & " FROM CommsUsers cm"
strSql3 = strSql3 & " LEFT JOIN Phones ph"
strSql3 = strSql3 & " ON cm.ID=ph.ID;"
' Create new Jet database
Set Cat = CreateObject("ADOX.Catalog")
Cat.CREATE strConJet
' 'inherit' the connection
Set Con = Cat.ActiveConnection
Set Cat = Nothing
With Con
' Create tables
.Execute strSql1
.Execute strSql2
' Create some sample data
.Execute "INSERT INTO CommsUsers (ID, lname, fname)" & _
" VALUES (1, 'Livehulas', 'A')"
.Execute "INSERT INTO CommsUsers (ID, lname, fname)" & _
" VALUES (2, 'Katewudes', 'B')"
.Execute "INSERT INTO CommsUsers (ID, lname, fname)" & _
" VALUES (3, 'Hevitoxic', 'C')"
.Execute "INSERT INTO CommsUsers (ID, lname, fname)" & _
" VALUES (4, 'Norarules', 'D')"
.Execute "INSERT INTO Phones (ID, Phone)" & _
" VALUES (1, '123')"
.Execute "INSERT INTO Phones (ID, Phone)" & _
" VALUES (1, '456')"
.Execute "INSERT INTO Phones (ID, Phone)" & _
" VALUES (2, '555')"
.Execute "INSERT INTO Phones (ID, Phone)" & _
" VALUES (2, '444')"
.Execute "INSERT INTO Phones (ID, Phone)" & _
" VALUES (4, '789')"
' Update syntax
.Execute "UPDATE CommsUsers SET lname = 'Luxasonic'" & _
" WHERE ID=1"
' UPDATE syntax
.Execute "DELETE FROM Phones WHERE ID=4"
End With
' Open recordset
Set rs = Con.Execute(strSql3)
' Copy data to ThisWorkbook
Set oTarget = ThisWorkbook.Worksheets(1) _
.Range("A1")
With rs
For lngCounter = 1 To .fields.Count
oTarget(1, lngCounter).Value = _
.fields(lngCounter - 1).Name
Next
End With
oTarget(2, 1).CopyFromRecordset rs
Con.Close
End Sub
3. How to ask for help with Jet and concurrent access - is there an issue at
all?
In this ng, of course! Perhaps there is a more appropriately titled
one (e.g. microsoft.public.data.ado) but this one is better
subscribed. For example, see this recent thread:
http://groups.google.com/[email protected]
You may have some luck in the MS Access newsgroups but IMO the
regulars there have trouble differentiating between MS Access and Jet
i.e. it's difficult to get a pure Jet perspective there.
Jamie.
--
