Connect to an ACCESS database using a DSN

Business Central/NAV Developers Forum
1

I would like to use a DSN to connect to an ACCESS database to send and receive some data. I found a way to do it with the path as the Data Source, but I would prefer to do it in a correct way using a DSN. Right now, I’m using the following lines: ----------------------- strDriver := ‘Provider=Microsoft.Jet.OLEDB.4.0’; // MSACCESS strDataSource := ‘Data Source=c:\tmp\myDB.mdb’; // could be a DSN? strOptions := ‘Persist Security Info=False’; strConnString := strDriver + ‘;’ + strDataSource + ‘;’ + strOptions; // New connection CREATE(varConnectionDB); varConnectionDB.Open(strConnString); // opens the connection -------------------[/size=1] The connection variable is an Automation, subtype: ‘Microsoft ActiveX Data Objects 2.5 Library’.Connection Does anyone know how I could use a DSN instead of my direct path?

2

Create a DSN and connect in the same way. CREATE(varConnectionDB) varConnectionDB.Open(‘DSNName’);

3

Here’s an example using SQL7 CREATE(“ADO-Connection”); CREATE(“ADO-RecordSet”); “ADO-Connection”.Open(‘DSN=[DataSourceName];UID=sa;PWD=4me2no’); Char := 39; - Char type variable Quote := FORMAT(Char); - Text Type Variable QStr := Quote + ‘,’ + Quote; InsertCommand := 'Insert Into [TableName] ‘; InsertData := ’ Values (’ + Quote + Value1 + QStr + Value2 + Quote + ‘)’; “ADO-Connection”.Execute(InsertCommand InsertData); This information is kept in the dark by gnomes. [:)] Good Luck

4

If I do like you say, I have the following error:

quote:

The call to member Open failed. Microsoft OLE DB Provider for ODBC Drivers returned the following message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I also tried to specify the provider: ‘Provider=Microsoft.Jet.OLEDB.4.0’, but the system returned:

quote:

Could not find installable ISAM

Forgive my hard head, but can I ask what type of DSN you are using? Or can I have an example more complete? Thank you in advance, David

quote:

Originally posted by ajayjain
Create a DSN and connect in the same way. CREATE(varConnectionDB) varConnectionDB.Open(‘DSNName’);

5

Before SQL7 we used the following incantation… “ADO-Connection”.ConnectionString(‘Data Source=’ + DbPath + DbName); “ADO-Connection”.Open(‘PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=’ + DbPath + DbName + ‘;’); If you’re trying with SQL7 … it appears that Navision isn’t finding a Data Source Name … You need to define it using the “System” tab of the ODBC Administrator. A tip that always helps me is to test the ODBC links using Excel to read some data using the same source. Good Luck,

6

david i made a system dsn and it is working fine. and i hope you must be know how to create a dsn [:D]

7

Thank you guys… It’s working for SQL, so I’ll just manage the ISAM error when using ACCESS (probably an error with the MS Jet version).