Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Access MDB files with Excel
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 -- Cheers Nigel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Access MDB files with Excel
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" wrote in message ... 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 -- Cheers Nigel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Access MDB files with Excel
Thanks Bob simple really!
-- Cheers Nigel "Bob Phillips" wrote in message ... 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" wrote in message ... 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 -- Cheers Nigel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access to excel files not on drive C | Setting up and Configuration of Excel | |||
How come I can't access my own excel files anymore? | Excel Discussion (Misc queries) | |||
covert excel to access files | Excel Discussion (Misc queries) | |||
Can't access excel files from worksheet | Excel Discussion (Misc queries) | |||
Using VBA to access files in a list in EXCEL | Excel Programming |