We are planning to implement Navision 3.6 on SQL 2000 in the next few months. One of our concerns is having the ability to automatically update some of our already existing Access 97 databases. Has anyone attempted this, and will we be able to automate it. Thanks, Mike [8D]
Mike, What exactly is it that you are trying to update? Access 97 to SQL 2000? Then Microsoft go very simple tools for that! Or are you trying to update your data in the Access data with the purpose of putting it into Navision 3.60? Then yes, you CAN DO IT, but unless you talk about LOTS OF DATA (and I mean lots’ - giga bytes of data or more), then the better solution is to use Dataports from Navision and import it, because this way is the easiest you are able to get everything right - because you cannot call the trigger codes externally - not even from SQL. If the
If the Access database is visible from the SQL Server machine, you can set it up as a linked server to SQL Server. Then, in Attain you can use a Linked Object table to point to Views into the tables on the linked server. This gives you the Access tables in Attain as regular tables that can be utilised from C/AL as normal tables, with the exception of transaction handling (which you wont get anyway if you go the dataport route for exporting data into files and then into the tables). This is the tightest integration and gives you almost all Attain features on top of your Access tables. But you need to set up security manually in SQL Server for access to the linked server, i.e. to Access.
We have managed to acces a Acces2k DB with a M$ Automation control(AC), this AC is installed/registered by default. If you’re instrested mail me… Features all SQL statements like insert/select/enz [8D] A db connection can be accomplished by ODBC or just by opening then db_file… – Met vriendelijke groet, best regards, Rik van Duuren Developer Mailto:rdu@columbus.nl Columbus IT Partner BV www.columbus.nl
We tried to extract data with access 97 from an SQL2000 Server and found that there is a problem with the number type conversion. At least in the German Version. The ODBC Interface seems not to be able to convert the 1,000 (for us 1k) correctly into the 1.000 (1k in german notation). Therefore the numbers extracted are of no use. I tried the Microsoft help line and - what do you expect - go no help. Perhaps someone has different experience… Regards, Carsten.
quote:
Originally posted by Michael21
We are planning to implement Navision 3.6 on SQL 2000 in the next few months. One of our concerns is having the ability to automatically update some of our already existing Access 97 databases. Has anyone attempted this, and will we be able to automate it. Thanks, Mike [8D]
Well this is strange. The DECIMAL data type in SQL Server, which is used by Attain for example for its Decimal type, can be read by the SQL Server ODBC driver only as character since there is no native C type that handles a fixed decimal. (I’m ignoring the possiblitly of using the struct for a decimal values). However, that character value is NOT sensitive to regional settings and always uses a ‘.’ for the decimal separator and nothing for a thousand separator. This is part of the ODBC specification, in the data type BNF in the appendex. This is what Attain is in fact doing, then converting the character string to an internal BCD value. So it is not the case that the ODBC interface cannot interpret the DECIMAL value. What might be happening after this, is that the character string is being converted to respect regional settings for the purposes of display. E.g., Query Analyzer will do this to display a decimal value correctly for your locale - because you want to see it ‘correctly’ as a user. But since Access is trying to consume the value as a raw value, I cant see why it should be converted for display purposes. The only thing I can think of is that the the data source you are using when linking into Access has the setting ‘Use regional settings when outputting currency, numbers, dates and times’ checked. This might give a ‘display aware’ decimal to Access. I will try out some of these ideas.
I tried with the setting I mentioned both on and off with no problems, but I am using Access XP so you might have a different scenario with Access 97. Perhaps you can try what I suggested and see if it works.