PDA

View Full Version : Create an access database by a VBA in Excel



Blasphemer
01-21-2009, 11:24 PM
Hi,

I am looking to create an Access database from VBA in Excel. But I am unable to "create the database" and or open one if its already created. Here's the code I have so far. Any help?



Option Explicit

Public Sub sub_SendToAcces()

Dim accApp As Access.Application
Dim accDB As Object

Set accApp = New Access.Application
' code to create a new database

' code to open it and set it in accDB variable

End Sub

GTO
01-22-2009, 12:30 AM
Cross-posted at: http://www.mrexcel.com/forum/showthread.php?t=365845

(two minutes before this thread started)

Blasphemer
01-22-2009, 12:52 AM
hehe yeah I just make sure i get my answers :D Thanks buddy

Jan Karel Pieterse
01-22-2009, 12:56 AM
This creates a database file:
'reference: Microsoft ActiveX Data Objects 2.7 Library
'reference: Microsoft ADO Ext. 2.7 for DLL and Security

Dim moADOconn As adodb.Connection

Sub CreateDatabase()
'-------------------------------------------------------------------------
' Procedure : CreateDatabase Created by Jan Karel Pieterse
' Company : JKP Application Development Services (c) 2006
' Author : Jan Karel Pieterse
' Created : 10-5-2006
' Purpose : Creates a Log Database
'-------------------------------------------------------------------------
Dim oADOXcat As ADOX.catalog
Set oADOXcat = New ADOX.catalog
oADOXcat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=""" & ThisWorkbook.Path & "\Log.mdb"";"
Set oADOXcat = Nothing
End Sub

Sub CreateTable()
'-------------------------------------------------------------------------
' Procedure : CreateTable Created by Jan Karel Pieterse
' Company : JKP Application Development Services (c) 2006
' Author : Jan Karel Pieterse
' Created : 10-5-2006
' Purpose : Creates Table in Log Database
'-------------------------------------------------------------------------
' MyText TEXT(50),
' MyMemo MEMO,
' MyByte BYTE,
' MyInteger INTEGER,
' MyLong LONG,
' MyAutoNumber COUNTER,
' MySingle SINGLE,
' MyDouble DOUBLE,
' MyCurrency CURRENCY,
' MyReplicaID GUID,
' MyDateTime DATETIME,
' MyYesNo YESNO,
' MyOleObject LONGBINARY,
' MyBinary BINARY(50)
Dim sSQL As String
OpenDatabase
sSQL = "CREATE TABLE tblLogResults " & _
"(ctrIndex COUNTER, DateAndTime DATETIME, CurrentStage TEXT(255), " & _
"CurrentAction TEXT(255), WorkBook TEXT(255), ObjectType TEXT(255), " & _
"Description TEXT(255), Value1 LONG)"
With moADOconn
.Execute sSQL
End With
End Sub

Sub OpenDatabase()
'-------------------------------------------------------------------------
' Procedure : OpenDatabase Created by Jan Karel Pieterse
' Company : JKP Application Development Services (c) 2006
' Author : Jan Karel Pieterse
' Created : 10-5-2006
' Purpose : Opens Connection to Log Database
'-------------------------------------------------------------------------
If moADOconn Is Nothing Then
Set moADOconn = New adodb.Connection
moADOconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=""" & ThisWorkbook.Path & "\Log.mdb"";"
End If
End Sub

Blasphemer
01-22-2009, 02:30 AM
Thanks buddy