Web Development

Ask something
Discussion Locked

VB6 Migration from Access to SQL Server

0 helpful
5 replies

by charlie_scott

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have an existing application that has grown from a normal 1 concurrent user system to about 8 concurrent users. The initial application was written accessing a MS Access database. I would like to migrate it SQL Server and it seems I have a couple of options; rewriting the application to connect and manipulate the data in SQL Server, or move the data to SQL Server and use MS Access with linked tables. The later seems like it could be implemented much quicker. Any comments to help me select the approach I take are appreciated.

5 replies

by john.a.wills

Using MS Access with linked tables would certainly be easier, as you could keep the VBA code and forms unchanged.

by billscott

your vb code should change little, you might need to change your delimiters around dates in your queries. your connection string will definitely change, one of these should do the trick for you:

Sql Standard = "provider=SQLSRV32;Driver={SQL Server};Server=[SERVER];Database=[DEFAULTDB];Uid=[USERID];Pwd=[PASSWORD];"
SQL Trustted = "provider=SQLSRV32;Driver={SQL Server};Server=[SERVER];Database=[DEFAULTDB];Trusted_Connection=yes;"
SQL OLEDB Trusted = "provider = SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=[DEFAULTDB];Data Source = [SERVER]"

by billscott

your vb code should change little, you might need to change your delimiters around dates in your queries. your connection string will definitely change, one of these should do the trick for you:

Sql Standard = "provider=SQLSRV32;Driver={SQL Server};Server=[SERVER];Database=[DEFAULTDB];Uid=[USERID];Pwd=[PASSWORD];"
SQL Trustted = "provider=SQLSRV32;Driver={SQL Server};Server=[SERVER];Database=[DEFAULTDB];Trusted_Connection=yes;"
SQL OLEDB Trusted = "provider = SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=[DEFAULTDB];Data Source = [SERVER]"

by jlanus

As of date format, in ADO I use the ODBC std escape and it works with all bases:
{d '2004-02-28'} for example. Note the zero in 02.

Function SQLdate(d As Date) As String
'============================================================
' Retorna la fecha d editada en formato standard ODBC {d 'yyyy-mm-dd'}.
SQLdate = "{d '" & Format$(d, "yyyy\-mm\-dd") & "'}"
End Function

There are escapes for timestamps and times too.
This might let you make your app more compatible before migrating.

by charlie_scott

I am not sure why I would be concerned with the connect string within the VB program. I was thinking when I linked the SQL Server tables to Access I would then just use access as I do today. Am I missing something?

This discussion is closed to new replies.