Using Access MDB files with Excel

[Nigel]

Hi All
For some time I have been linking an Excel query to a pre-built MDB file. I
know that you do not need Access on the target machine for this to work.

My questions are...

1. Can by using Excel add new records to an existing MDB without Access
being present, and how?
2. Can you create an MDB file from VBA without Access, and how?

TIA

 

[Bob Phillips]

With ADO and the Access drivers, you should be able to.

This adds records

Sub InsertData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "INSERT INTO Contacts (FirstName, LastName, Phone, Notes) " & _
" VALUES ('Phillips', 'Bob', 'None', '')"

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

Set oRS = Nothing
End Sub




This creates a file and tables


'----------------------------------------------------------------
Sub CreateAccessDatabase()
'----------------------------------------------------------------
Dim oADOCat As Object
Set oADOCat = CreateObject("ADOX.Catalog")

oADOCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\bob.mdb"

Set oADOCat = Nothing
End Sub



'----------------------------------------------------------------
Sub CreateAccessTable()
'----------------------------------------------------------------
Dim oADOCat As Object
Dim oTable As Object

Set oADOCat = CreateObject("ADOX.Catalog")
oADOCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\bob.mdb"
Set oTable = CreateObject("ADOX.Table")

' Create a new Table object.
With oTable
.Name = "Contacts"
' Create fields and append them to the
' Columns collection of the new Table object.
With .Columns
.Append "FirstName", 202
.Append "LastName", 202
.Append "Phone", 202
.Append "Notes", 203
End With
End With

' Add the new Table to the Tables collection of the database.
oADOCat.Tables.Append oTable

Set oTable = Nothing
Set oADOCat = Nothing
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Nigel]

Thanks Bob simple really!

--
Cheers
Nigel

With ADO and the Access drivers, you should be able to.

This adds records

Sub InsertData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "INSERT INTO Contacts (FirstName, LastName, Phone, Notes) " & _
" VALUES ('Phillips', 'Bob', 'None', '')"

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

Set oRS = Nothing
End Sub




This creates a file and tables


'----------------------------------------------------------------
Sub CreateAccessDatabase()
'----------------------------------------------------------------
Dim oADOCat As Object
Set oADOCat = CreateObject("ADOX.Catalog")

oADOCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\bob.mdb"

Set oADOCat = Nothing
End Sub



'----------------------------------------------------------------
Sub CreateAccessTable()
'----------------------------------------------------------------
Dim oADOCat As Object
Dim oTable As Object

Set oADOCat = CreateObject("ADOX.Catalog")
oADOCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\bob.mdb"
Set oTable = CreateObject("ADOX.Table")

' Create a new Table object.
With oTable
.Name = "Contacts"
' Create fields and append them to the
' Columns collection of the new Table object.
With .Columns
.Append "FirstName", 202
.Append "LastName", 202
.Append "Phone", 202
.Append "Notes", 203
End With
End With

' Add the new Table to the Tables collection of the database.
oADOCat.Tables.Append oTable

Set oTable = Nothing
Set oADOCat = Nothing
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

file.