General discussion

Locked

VB6 Migration from Access to SQL Server

By charlie_scott ·
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.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by john.a.wills In reply to VB6 Migration from Access ...

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

Collapse -

by BillScott In reply to VB6 Migration from Access ...

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]"

Collapse -

by BillScott In reply to VB6 Migration from Access ...

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]"

Collapse -

by jlanus In reply to VB6 Migration from Access ...

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.

Collapse -

by charlie_scott In reply to VB6 Migration from Access ...

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?

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums