Creating and populating an MDB file from Navision

RobbieX

Based on the VB code below 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 dbs, tdf and fld be?
Your advice would be truly appreciated!

VB 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

RobbieX

I have looked around for help on the web and have now come up with the following code which I think is heading in the right direction but I am still having difficulty. Can anyone advise PLEASE?

DataConnection : Automation 'Microsoft ActiveX Data Objects 2.0 Library'.Connection
DataRecSet : Automation 'Microsoft ActiveX Data Objects 2.0 Library'.RecordsetCREATE(DataConnection);

CREATE(DataConnection);
DataConnection.ConnectionString := 'DBQ=C:\Newdb.mdb; DRIVER={Microsoft Access Driver (*.mdb)}';
DataConnection.Open;
CREATE(DataRecSet);
DataRecSet.ActiveConnection := DataConnection;
DataRecSet.CursorType := 1;
DataRecSet.LockType := 4;
DataRecSet.Open('Table1');

IF NOT (DataRecSet.BOF AND DataRecSet.EOF) THEN BEGIN
DataRecSet.MoveFirst;
REPEAT
DataRecSet.Delete(1);
DataRecSet.MoveNext
UNTIL DataRecSet.EOF;
END;
DataRecSet.AddNew;
DataRecSet.Fields.Item(0).Value := '1';
DataRecSet.Fields.Item(1).Value := '2';
DataRecSet.Fields.Item(2).Value := '3';
DataRecSet.Fields.Item(3).Value := '4';
DataRecSet.Update;
DataRecSet.UpdateBatch;
DataRecSet.Close;

RobbieX

Great news for me. This has now been solved thanks to my friend Jaska in Thailand! Thank you Jaska! Here is the solution for those that are interested....

In a new codeunit, declare the variables you need:

Name DataType Subtype Length
DataConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
DataRecSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
DataCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command
xCat Automation 'Microsoft ADO Ext. 2.8 for DDL and Security'.Catalog
xDBPath Text 250
xCmdTxt Text 250
ConStr Text 250

Then add this code:

//Set the Path and Connection String
xDBPath := 'C:\Newdb.mdb;';
ConStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + xDBPath ;

//Create New MDB database
CREATE(xCat);
xCat.Create(ConStr);

//Create New table
CREATE(DataConnection);
DataConnection.ConnectionString := ConStr;
DataConnection.Open;
xCmdTxt := 'Create Table Table1 ('+'field1 Text(20),'+ 'field2 Text(20),'+'field3 Text(20),'+ 'field4 Text(20)'+ ')';
DataConnection.Execute(xCmdTxt);

CREATE(DataRecSet);
DataRecSet.ActiveConnection := DataConnection;
DataRecSet.CursorType := 1;
DataRecSet.LockType := 4;
DataRecSet.Open('Table1');

IF NOT (DataRecSet.BOF AND DataRecSet.EOF) THEN BEGIN
DataRecSet.MoveFirst;
REPEAT
DataRecSet.Delete(1);
DataRecSet.MoveNext
UNTIL DataRecSet.EOF;
END;
DataRecSet.AddNew;
DataRecSet.Fields.Item(0).Value := '1';
DataRecSet.Fields.Item(1).Value := '2';
DataRecSet.Fields.Item(2).Value := '3';
DataRecSet.Fields.Item(3).Value := '4';
DataRecSet.Update;
DataRecSet.UpdateBatch;
DataRecSet.Close;

MESSAGE('Success! Your MDB database has been created.');
\:D/