Convert VB6 client/server with Access 2000 to SQL 7

[vincentmairiaux]

I've written in VB6 for my company a complete client/server back-office application using ODBC to Access 2000 databse and all works perfect.

However, we recently had an accountancy program installed on the server using an SQL 7 database, and that f...ing (sorry, he was just doing his job but ... you know) converted my beautiful Access database to SQL 7 format (stock, personal, orders, invoices and all the stuff).

1. It seems it is rather simple to make my application connect to the SQL database, but how?
2. Can I still use the Access UI to add/delete/edit some records in the SQL database?
3. What about queries and reports I defined in Access?

Thanks for all information and/or experience with it.
Vincent from Antwerp/Belgium

vincent.mairiaux@yucom.be

 

[shmiller]

1. You should just be able to change the connection string to connect to the SQL db. You should probably use the OLEDB drivers and ADO instead of ODBC.
2. As far as using your Access UI, you can change your code in the Access forms to use ADO and connect to the SQL db.
3. Reports are a little tricky. I've done some Access reports that get their data from a SQL db. I used pass-through queries. You will need to have a DSN set up for the SQL db, then you can set up pass-through queries and use those as the record sources for your reports. The way I did it was to have a table in the SQL db for report printing parameters. Then I used ADO to populate it and had stored procedures that got their parameters from that table. Then I used the pass-through queries to execute the stored procedures and get the recordset for the reports. If you want any more details, let me know.