Greeting,

I have the following code for creating mdb by clicking on button in workbook
sheet. The code is working fine but the problem is the mdb format is 2000 and
I need it to be 2003 format? Any suggestion please??

Public Const db_File = "My_db"
Sub CreateMydb()
Dim MyCatalog As New ADOX.Catalog
FullName = ThisWorkbook.Path & "\" & db_File & ".mdb"
On Error Resume Next
Kill FullName
On Error GoTo 0
MyConct = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FullName &
";"
MyCatalog.Create MyConct & ";Data Source = " & FullName
End Sub

I found sample code from the VBA Access help for a module CreateTableDefX

Public Const db_File = "My_db"
Sub CreateMydb()

Dim MyCatalog As New ADOX.Catalog
FullName = ThisWorkbook.Path & "\" & db_File & ".mdb"
FName = Dir(FullName)
If FName < "" Then
Kill FullName
End If

MyCatalog.ActiveConnection = CurrentProject.Connection

Set tbl = New ADOX.Table
tbl.Name = "Contacts"

With tbl.Columns
.Append "FullName", adVarChar, 20
.Append "Data Source", adVarChar, 20
End With

Set cmd = New ADODB.Command
With cmd
.CommandText = "Mycontact"
.CommandType = adCmdUnknown

.Parameters.Append .CreateParameter( _
"Fullname", adVarChar, adParamInput, 20)

.Parameters.Append .CreateParameter( _
"Data Source", adVarChar, adParamInput, 20)
End With
End Sub


Access code
-----------------------------------------------------------------

Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.
.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop < "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop < "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub




"Jon" wrote:

Greeting,

I have the following code for creating mdb by clicking on button in workbook
sheet. The code is working fine but the problem is the mdb format is 2000 and
I need it to be 2003 format? Any suggestion please??

Public Const db_File = "My_db"
Sub CreateMydb()
Dim MyCatalog As New ADOX.Catalog
FullName = ThisWorkbook.Path & "\" & db_File & ".mdb"
On Error Resume Next
Kill FullName
On Error GoTo 0
MyConct = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FullName &
";"
MyCatalog.Create MyConct & ";Data Source = " & FullName
End Sub

Joel,

I know nothing about this, but my interest was piqued by your question. I'm
not sure, but the impression I get is that this type of process is always
going to result in an Access 2000 format. I can't find anything that
indicates otherwise, and from Wikipedia and this post on Ozgrid ...

http://www.ozgrid.com/forum/showthread.php?t=40365

.... it looks like Jet 4.0 always creates an Access 2000?

I was wondering, if you are just creating an mdb that you are then opening
in Access, could you do the reverse and import the workbook from Access,
using TransferSpreadsheet?

good luck!

Doug


"Joel" wrote in message
...
I found sample code from the VBA Access help for a module CreateTableDefX

Public Const db_File = "My_db"
Sub CreateMydb()

Dim MyCatalog As New ADOX.Catalog
FullName = ThisWorkbook.Path & "\" & db_File & ".mdb"
FName = Dir(FullName)
If FName < "" Then
Kill FullName
End If

MyCatalog.ActiveConnection = CurrentProject.Connection

Set tbl = New ADOX.Table
tbl.Name = "Contacts"

With tbl.Columns
.Append "FullName", adVarChar, 20
.Append "Data Source", adVarChar, 20
End With

Set cmd = New ADODB.Command
With cmd
.CommandText = "Mycontact"
.CommandType = adCmdUnknown

.Parameters.Append .CreateParameter( _
"Fullname", adVarChar, adParamInput, 20)

.Parameters.Append .CreateParameter( _
"Data Source", adVarChar, adParamInput, 20)
End With
End Sub


Access code
-----------------------------------------------------------------

Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.
.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop < "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop < "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub




"Jon" wrote:

Greeting,

I have the following code for creating mdb by clicking on button in
workbook
sheet. The code is working fine but the problem is the mdb format is 2000
and
I need it to be 2003 format? Any suggestion please??

Public Const db_File = "My_db"
Sub CreateMydb()
Dim MyCatalog As New ADOX.Catalog
FullName = ThisWorkbook.Path & "\" & db_File & ".mdb"
On Error Resume Next
Kill FullName
On Error GoTo 0
MyConct = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FullName
&
";"
MyCatalog.Create MyConct & ";Data Source = " & FullName
End Sub

I was working from a book I bought Title Access 2003. I had to by the book a
number of years ago because I was working with an Access 97 database which I
had to keep in that format, but I had Office 2003 installed on my PC.

You are right that Acess 97 and 2000 use the Jet Blue engine and the new
Access uses the ADOX table. the original code deleted the old database that
existed with the kill command. I assumed th e posting wanted to open a new
database and configure it for Office 2003.

When working with Access objects from excel you either havve to use
CREATEOBJECT or use GETOBJECT to work with the database. Then you can use
any Access macro methods as long as you add the Access Reference to you Excel
project by using the menu option Tools - Referenced - Microsoft Access XX.X
Object Library.

Most people prefer when working with Access and Excel together to run the
macro from the Excel Environment. Access Macro Language doesn't allow random
access to the database. You first must locate the Row and then go to the
column. Excel allows complete random access going directly to the cell
location cells(row,column). Excel also allows you do do more commands. And
when you need an Access Method it is available.

"Doug Glancy" wrote:

Joel,

I know nothing about this, but my interest was piqued by your question. I'm
not sure, but the impression I get is that this type of process is always
going to result in an Access 2000 format. I can't find anything that
indicates otherwise, and from Wikipedia and this post on Ozgrid ...

http://www.ozgrid.com/forum/showthread.php?t=40365

.... it looks like Jet 4.0 always creates an Access 2000?

I was wondering, if you are just creating an mdb that you are then opening
in Access, could you do the reverse and import the workbook from Access,
using TransferSpreadsheet?

good luck!

Doug


"Joel" wrote in message
...
I found sample code from the VBA Access help for a module CreateTableDefX

Public Const db_File = "My_db"
Sub CreateMydb()

Dim MyCatalog As New ADOX.Catalog
FullName = ThisWorkbook.Path & "\" & db_File & ".mdb"
FName = Dir(FullName)
If FName < "" Then
Kill FullName
End If

MyCatalog.ActiveConnection = CurrentProject.Connection

Set tbl = New ADOX.Table
tbl.Name = "Contacts"

With tbl.Columns
.Append "FullName", adVarChar, 20
.Append "Data Source", adVarChar, 20
End With

Set cmd = New ADODB.Command
With cmd
.CommandText = "Mycontact"
.CommandType = adCmdUnknown

.Parameters.Append .CreateParameter( _
"Fullname", adVarChar, adParamInput, 20)

.Parameters.Append .CreateParameter( _
"Data Source", adVarChar, adParamInput, 20)
End With
End Sub


Access code
-----------------------------------------------------------------

Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.
.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop < "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop < "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub




"Jon" wrote:

Greeting,

I have the following code for creating mdb by clicking on button in
workbook
sheet. The code is working fine but the problem is the mdb format is 2000
and
I need it to be 2003 format? Any suggestion please??

Public Const db_File = "My_db"
Sub CreateMydb()
Dim MyCatalog As New ADOX.Catalog
FullName = ThisWorkbook.Path & "\" & db_File & ".mdb"
On Error Resume Next
Kill FullName
On Error GoTo 0
MyConct = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FullName
&
";"
MyCatalog.Create MyConct & ";Data Source = " & FullName
End Sub