Create an access database by a VBA in Excel

[Blasphemer]

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]

Cross-posted at: http://www.mrexcel.com/forum/showthread.php?t=365845

(two minutes before this thread started)

[Blasphemer]

hehe yeah I just make sure i get my answers Thanks buddy

[Jan Karel Pieterse]

This creates a database file:
[vba]'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[/vba]

[Blasphemer]

Thanks buddy