Creating and populating an MDB file from Navision

RobbieX
RobbieX Member Posts: 66
edited 2008-09-12 in NAV/Navision Classic Client
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

Answers

  • RobbieX
    RobbieX Member Posts: 66
    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
  • RobbieX
    RobbieX Member Posts: 66
    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/
    Robbiex

Categories