locked
Access to SQL Server migration - is VBA conversion needed? RRS feed

  • Question

  • Hello!

    I have clearly understand how to convert Access database tables to SQL Server. And that later I need to change source of Forms and Reports.

    Thanks for forum!

    But what about VBA code conversion. As I have understand all code that works with Forms - will working well, but what about recordsets?

    Both DAO and ADODB will work? What are the most popular changes in VBA should be done? Have not found such an information. Or nothing should be changed?

    Thursday, December 13, 2012 12:10 PM

Answers

  • Hello,

    You may find some issues with dates. It does not only affects fields on Forms, but is also the way dates are stored on SQL Server (yyyy-mm-dd hh:mm:ss) and the way are generated by VBA (dd-mm-yyyy).

    On VBA, strings are enclosed by double quotes. On T-SQL, strings are enclosed by single quotes.

    Date() and Now() functions should be replaced by CONVERT/CAST and GETDATE()

    InStr() and Mid() functions should be replaced by SUBSTRING in T-SQL.

    IIF function should be replaced by CASE statement.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, December 13, 2012 1:40 PM

All replies

  • Hello,

    You may find some issues with dates. It does not only affects fields on Forms, but is also the way dates are stored on SQL Server (yyyy-mm-dd hh:mm:ss) and the way are generated by VBA (dd-mm-yyyy).

    On VBA, strings are enclosed by double quotes. On T-SQL, strings are enclosed by single quotes.

    Date() and Now() functions should be replaced by CONVERT/CAST and GETDATE()

    InStr() and Mid() functions should be replaced by SUBSTRING in T-SQL.

    IIF function should be replaced by CASE statement.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, December 13, 2012 1:40 PM
  • In tables I have somewhere Now() functions. So, as I see - I should change it to GETDATE()

    But I think that I can leave my sql queries in Access database. Or I should obligatorily change them to T-SQL?

    Or may be I should change only VBA RecordSet queries to T-SQL?

    This part is not absolutely clearly for me. I wish export to SQL Server database only tables.


    Friday, December 14, 2012 5:17 AM
  • Hello,

    Those VBA queries have to be changed, because VBA functions used on those queries (like the ones I mentioned) won’t work against a SQL Server database.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Friday, December 14, 2012 1:52 PM
  • Thank you, Alberto!
    Monday, December 17, 2012 7:15 AM
  • Still not able to try convert my database to SQL Server, but have found atricle by Luke Chung and Dan Haught.

    In this article is described that there are two possibilities converting Access to SQL Server - with jet and without. With Jet looks much more easy. Of course it's not best way for work with multiple users, but in this case I will be able to write .Net application/site which will work with SQL Server tables.

    Access with tables on SQL Server

    This gives me some hopes that not much changes will be needed.

    Saturday, December 29, 2012 7:35 AM
  • Ta-da!
    Have tried today! Have convert only two tables to SQLServer tables.

    Everything works. Even Now(), Mid(), IIF() functions in my access queries ( I have not convert them to T-SQL ).

    But what I need to do manualy - are constraints. I have some >= constraints in nummeric columns.

    And default values as Now() should be added on SQL Server side.

    Friday, January 11, 2013 1:00 PM
  • Hello,

    Go to the design of the table, select the column name, on the Column Properties, use Getdate() as default value instead of Now().

    Some article about constraints:

     
    http://www.techrepublic.com/article/defining-sql-server-constraints-with-tsql/6181836

    http://www.databasejournal.com/features/mssql/article.php/3811831/Using-Check-Constraints-to-Validate-Data-in-SQL-Server.htm

    http://www.sqlusa.com/bestpractices2005/moneyformat/

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, January 11, 2013 2:52 PM