V8 MU ODBC to MSSQL

[wfl]

I have a File DSN located on a server (in the same mapping for all users as V8MU) that connects Excel to our Sage MSSQL db (2005) no probs. However if I try and use the same DSN through Actinic it tells me;

Actinic wasn't able to find the table for the DSN you selected. Please select another database

If I try and use a System DSN (that also works OK with Excel) it will see the tables but as soon as I select one Actinic tells me;

There is a problem with ODBC linking.

This can happen if you are sharing the same Actinic database from more than one computer, and the data source you are linking to is not on the same drive and path from all of the computers. e.g. the data source is on the C drive as seen from one computer, but cannot be seen from by the other. Second example, the data source is on the R drive from one computer, but is on the S drive from the other computer.

....and won't go any further.

The inference being that I cannot connect to an external db from MU.....?

[Jan]

You can link to external databases using MU, but you must map them in the same way on all computers - so for example, if it is on your C drive, you need to map a drive to say Z on your computer and on all of the other computers that want to access it.

There is one exception to the rule though, excel cannot be linked to in MU because excel files are single user.

Regards,

[wfl]

You can link to external databases using MU, but you must map them in the same way on all computers - so for example, if it is on your C drive, you need to map a drive to say Z on your computer and on all of the other computers that want to access it.

Exactly that Jan. All of our PCs have a hard map using the same drive letter to the same network sites folder.

[Jan]

What type of file are you trying to link in?

Regards,

[wfl]

Hi Jan

I am looking at what I can link to Sage MMS (now rebranded Line 200 ).

We are running it on a MS SQL 2005 db.

So I am simply looking in the first instance to pull in the the Stock table.

One option I was going to try was to create an intermediary table in SQL to populate it with all the data I want to go to Actinic.

[Jan]

Maybe it's a SQL server 2005 issue then, I've linked to 2000 but not to 2005. Two things to check, make sure that the server has mixed mode security selected and make sure that the table has a primary key.

If you can't get it to work, you could create your intermediate table in access and link to that (don't use excel because that would only give you a one way link).

Regards,

[wfl]

Hi Jan

Mixed mode is on and I imagine one or more of the tables I have tried have primary keys - they are standard Sage tables.

I don't think it will be 2005 or a driver issue but I will see if there is a later MDAC driver for 2005.

The thing is that I can use that DSN to get data from the same SQL tables into Excel, Access and Crystal.

All of which points to MU causing the problem.

[Jan]

I've checked this, it's nothing to do with MU specifically, it seem to be a limitation of Actinic generally. I get the same error but can connect to SQL 2000, access etc.

You need to report a bug to Actinic I think and try the workaround I have suggested in the mean time.

Regards,

[wfl]

...it seem to be a limitation of Actinic generally.

Noooo.. really!

If you recall a year or so ago I was struggling to get Actinic to link to Visual Fox. Although that was a dbx problem it was what got me started on the SQL route.

I will add this one to my list.

[Jan]

Yes, I do remember you now, I'll update my list as well.

Regards,