Creating MS Access DB from C/AL

sanjeev_baranwal

Can someone help me for how to create a MS access DB and a table from Navision through code. Waiting for reply :?:

Miklos_Hollender

Hm... maybe copying an existing empty MDB through a SHELL command?

For table, ADO? http://mibuso.com/dlinfo.asp?FileID=589

DenSter

I would start by trying the Access object model. Search for a VBA code snippet and try to replicate it in Navision with the use of automation variables. Check out the Excel buffer table to see how it is done with Excel automation, you can probably do something similar with the Access object model.

sanjeev_baranwal

Hi thanks for the reply.
By the way Miklos It is a good trick to copy the mdb file but my question is how to create through code. and Daniel I think I must use ADOX in automation and then create tables in it through it. I am trying to do it and how it end wil let u know and will also loook for Access Object Model. I will try first to complete in vb and then write the corresponding in Navision.

fl

Hi,

I just looked around on library of mdsn.com, and found following code:

' Include following in Declarations section of module.
Dim appAccess As Access.Application

Sub NewAccessDatabase()
Dim dbs As Object, tdf As Object, fld As Variant
Dim strDB As String
Const DB_Text As Long = 10
Const FldLen As Integer = 40

' Initialize string to database path.
strDB = "C:\My Documents\Newdb.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application.9")
' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
' Get Database object variable.
Set dbs = appAccess.CurrentDb
' Create new table.
Set tdf = dbs.CreateTableDef("Contacts")
' Create field in new table.
Set fld = tdf. _
CreateField("CompanyName", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
Set appAccess = Nothing
End Sub

This example above makes use of access object. I would not myself create tables like that. I really prefer ADO and DDL way. By using DDL (data definition language, is comparable to sql but then specific commands to create tables, fields, keys, relations and so on) you have all the possibilties like mentioned below

dim strsql as string
dim cn as adodb.connection

cn.connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=databaseName;User ID=MyUserID;Password=MyPassword;"

User ID and Password must not be used i thought
Data Source= c:\mydatabase.mdb
provider = like mentioned above to connect to a ms access database


strsql=CREATE TABLE tblCustomers (
CustomerID INTEGER NOT NULL,
[Last Name] TEXT(5) NOT NULL,
[First Name] TEXT(50) NOT NULL,
Phone TEXT(10),
Email TEXT(50))

cn.Execute (strsql)

table tblCustomers is now created =D>

This command uses ado (Be carefully, adox is a smaller footprint version of ado, i allways use ado. I just thought that using DDL was one of the points that was not implemented all the way in adox, but i don't know exactly by heart for the moment)


This should help you to start with.

Francois

sanjeev_baranwal

Hi Francois,
u have given a reply which can solve my problems I am sure.

RobbieX

Based on the code provided by Francois I am trying to replicate it in C/AL but I am finding it a bit tricky.
This is what I have so far:

DB_Text := 'Sony';
FldLen := 40;

g_txtNewDatabase := 'C:\Newdb.mdb';
CREATE(AccessApp);
AccessApp.NewCurrentDatabase(g_txtNewDatabase);
dbs := AccessApp.CurrentDb;
tdf := dbs.CreateTableDef("Contacts");
fld := tdf.CreateField("CompanyName", DB_Text, FldLen);
tdf.Fields.Append(tdf);


It seems to create the database okay, but I am having trouble creating the tables and fields.
What data types should bds, tdf and fld be?
Your advice would be truly appreciated!