Get DB connection to MS Acceess from NAV 2009 R2

Gabry
Gabry Member Posts: 48
edited 2013-09-10 in NAV Three Tier
I know how to get the connection from MS SQL Server (see code below), but not from MS Access (<file_name>.mdb). I am not able to get the string connection data from MS Access.

Do you know how to open the connection?
SQLServerName := 'PC16\SQLEXPRESS';
SQLUserID := 'AlfaViewer';
SQLPassword := 'xxx';
SQLDB := 'D2000Sql';

IF lRtc THEN BEGIN
  SQLConnectionString := 'Server='+SQLServerName + ';'
  + 'Database='+SQLDB + ';'
  + 'User=' + SQLUserID + ';'
  + 'Password=' + SQLPassword + ';'
  + 'Connection Timeout=20;';

  SQLConnection := SQLConnection.SqlConnection(SQLConnectionString);

  txtStartDate  := FORMAT(StartDate,8,'<Year4><Month,2><Day,2>');
  txtEndDate  := FORMAT(EndDate,8,'<Year4><Month,2><Day,2>');

  SQLConnection.Open;
  SQLCommand := SQLConnection.CreateCommand();
  IF SalesPoint.GET(SalesPointCode) THEN
    SQLCommand.CommandText := 'select * From [view_MultiTabella] ' +
                              'where (DataReg BETWEEN ''' + 
                                      txtStartDate + ''' AND ''' +
                                      txtEndDate + ''') AND (Impianto = ''' + SalesPointCode + ''')'
  ELSE
    SQLCommand.CommandText := 'select * From [view_MultiTabella] ' +
                              'where (DataReg BETWEEN ''' + 
                                      txtStartDate + ''' AND ''' + 
                                      txtEndDate + ''')';

  SQLReader := SQLCommand.ExecuteReader;
END;

Comments

  • Gabry
    Gabry Member Posts: 48
    I replaced the SQL provider with ODBC provider as it is shown in the code below. But running it using RTC the following error occurs:

    "This message id for C/AL programmers: The call to member System.Data.Odbc.OdbcConnection failed: Format of the initialization string does not conform to specialization starting at index 0."

    I suppose that is a problem with connection string: do you know how to set it for MS Access?
      SQLConnectionString := 'C:\AF\DecryptDatabase.mdb';
  ODBCConnection := ODBCConnection.OdbcConnection(SQLConnectionString);
  ODBCConnection.Open();
  ODBCCommand := ODBCConnection.CreateCommand();
  ODBCCommand.CommandText := 'select * from CarichiEff where CaricoEff_Numero = 1';
  ODBCReader := ODBCCommand.ExecuteReader();

Categories