I have an old VB6 application which works well.
For performance it runs on the server that where the database resides.
The server OS is Windows Server 2003 R2 x64
It processes log files and imports the data into an SQL 2005 database after validating the data in each log file. There can be over 50 files per day and each log file can contain more than 5000 records. The database grows at approx 5GB every six months. There are approx 45 tables in the database.
I connect to the database via code below.
The database is interogated by opening recordsets as needed and all insertions are done by building the sql statement in a string varaible and issuing a gcnADO.Execute command to insert/update a record on any given table table.
I want to convert this application to VS2008, visual basic.
What is the best practice to do this ?
Public gcnADO As ADODB.Connection
Public cmd As New ADODB.Command
Public Function ConnectToDatabase(ByVal strServer As String, ByVal strPassword As String, ByVal sDBName As String) As Boolean
On Error GoTo Error_ConnectToDatabase
Dim strConnect As String
Dim strError As String
ConnectToDatabase = False
Set gcnADO = New ADODB.Connection
strConnect = "Data Source = " & strServer
gcnADO.Provider = "SQLOLEDB.1"
gcnADO.Open strConnect, gstrUserID, strPassword
gcnADO.DefaultDatabase = sDBName
ConnectToDatabase = True
strError = "Cannot Open Database Error No :" & Err.Number & " Description :" & Err.Description
MsgBox strError, vbCritical, "Critical Error"