Vb6 app on windows 7 using sql server 2000

Hi guys!

Im new to this group and i wanted to ask for assistance on how i can connect our app to windows 7 32 bit. I had upgraded the sql from sql 7 to sql 2000 and sql 2005. Also restored the sql 7 database to sql 2000 and sql 2005. My problem is the database connection also furnish odbc connection. My app cant start up since it connects to sql before you can login. Help pls.

Please let me know what i can upload to help me on this matter. Thanks everyone looking forward for some expert advise. (:

it runs well on xp and xp mode windows 7. but i want it to run on windows 7 from now on. thanks everyone! cheers!

Sub Main()
Dim dumString As String
kIniFile = App.Path & "\irisretail.ini"
DoEvents
If Not IniFound Then
Exit Sub
End If
ReadINI
' opens the database
' FOR CLIENTDATA
dumString = App.Path & "\" & viniMain
If Not ADOOpenConnection(dumString, 1, 0) Then
MsgBox ("Error connecting to database " & viniMain)
End
End If
DoEvents
dumString = App.Path & "\MasterStore\irisClient.mdb"
If Not ADOOpenConnection(dumString, 1, 2) Then
MsgBox ("Error connecting to database " & dumString)
End If
'get the global variables
getOrgParam
dumString = App.Path & "\" & viniMain
'If Not ADOOpenConnection(dumstring, 3, 1) Then
'FOR SQL
If Not ADOOpenConnection(dumString, vServerType, 1) Then
MsgBox ("Error connecting to <sql server> database " & glob.serverdatabase & "@" & glob.dataservername)
GoTo ServerOffLine
End
End If

'12/11/2003 mat
getSysParam
'xxxxxxxx


'SetListeningPort
dispglobaltoMDI
selectModules kPCPModule, vModStat
' MDIForm1.Show
'10/21/2003 mat
' PostPCP

'Form1.Show
Exit Sub
ServerOffLine:
MDIForm1.mnuLogin.Enabled = False
MDIForm1.Show
End Sub

Public Function ADOOpenConnection(ByVal database As String, _
ByVal FileType As Integer, _
Index As Integer)

' This function serves as a generial tool for opening a database with
' the ffg. extensions *.mdb,*.dbf. and sql files

Dim DProvider As String
On Error GoTo errmsg
Const DBFFiles = 0
Const MDBFiles = 1
Const SQLFiles = 3
Const MDB2000Files = 4
Const RemoteMDB = 5
'05/26/2000 NES This is TO Filter SQL Server to MSAccess 2000
'02/11/2004 MAT
'If FileType = SQLFiles Then FileType = MDB2000Files
'XXXXX
Dim DatabaseName As String
Dim servername As String
Select Case FileType
Case RemoteMDB
'DProvider = "Provider=MS Remote;"
' DProvider = DProvider & "Data Source=" & glob.webDSN & ";"
' DProvider = DProvider & "Remote Server=" & glob.URL
DProvider = "Provider=MS Remote;Remote Server=" & glob.URL
DProvider = ";Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob.webDSN

Case MDB2000Files
DProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
DProvider = DProvider & "Persist Security Info=False;"
DProvider = DProvider & "Data Source=" & glob.serverdatabase

Case SQLFiles
'for SQL Server connection
'DProvider = "SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MCHD;data source=RSAWebServer" + ";UID=carlos;pwd=cty123"
'DProvider = "Server=RSAWebServer;Database=MCHD;UID=carlos;PWD=cty123;"
'DProvider = "Server=" & glob.dataservername & ";Database=" & glob.serverdatabase & ";UID=" & glob.dataserveruid & ";PWD=" & glob.dataserverpassword & ";"
DProvider = "Server=" & glob.dataservername & ";Database=" & glob.serverdatabase & ";UID=" & glob.dataserveruid & ";"
'DProvider = DProvider & ";BackgroundFetch=Yes;"
Case DBFFiles
DProvider = "Provider=MSDASQL.1;Persist Security Info=False;"
DProvider = DProvider & "Extended Properties="
DProvider = DProvider & """" & "DSN=dBase Files - Word;UID=;"
DProvider = DProvider & "SourceDB=" & database & ";SourceType=DBF;"
DProvider = DProvider & "Exclusive=No;Null=No"
'DProvider = DProvider & "Exclusive=No;BackgroundFetch=Yes;"
'DProvider = DProvider & "Collate=Machine;Null=Yes;Deleted=Yes;"
DProvider = DProvider & """"
'Case MDB2000Files
' DProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
' DProvider = DProvider & "Persist Security Info=False;"
' DProvider = DProvider & "Data Source=" & database

Case Else
'DProvider = "Provider=Microsoft.Jet.OLEDB.3.51;"
DProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
DProvider = DProvider & "Persist Security Info=False;"

'mat 05312007
DProvider = DProvider & "Jet OLEDB:Database Password=" & vDBPassword & ";"

DProvider = DProvider & "Data Source=" & database
'DProvider = DProvider & ";BackgroundFetch=Yes;"

End Select

Set cn(Index) = New ADODB.Connection
Select Case FileType
Case SQLFiles
With cn(Index)
.Provider = "sqloledb"
.CommandTimeout = 0
.Open DProvider
End With
Case Else
With cn(Index)
.ConnectionString = DProvider
.CommandTimeout = 30
.Open
End With
End Select

ADOOpenConnection = True
Exit Function
errmsg:
ADOOpenConnection = False
Exit Function
End Function

Can someone tell me whats wrong with this code please? this runs well on windows xp and sql 7 unfortunately not on windows 7. thanks.

Which connection string is actually being used when you have the issue?

Quote:

---

Originally Posted by DataMiser

Which connection string is actually being used when you have the issue?

---

Attachment 128713

When i click the exe it automatically looks for the sql database. Thats where it stops sir.

Sub Main()
Dim dumString As String
kIniFile = App.Path & "\irisretail.ini"
DoEvents
If Not IniFound Then
Exit Sub
End If
ReadINI
' opens the database
' FOR CLIENTDATA
dumString = App.Path & "\" & viniMain
If Not ADOOpenConnection(dumString, 1, 0) Then
MsgBox ("Error connecting to database " & viniMain)
End
End If
DoEvents
dumString = App.Path & "\MasterStore\irisClient.mdb"
If Not ADOOpenConnection(dumString, 1, 2) Then
MsgBox ("Error connecting to database " & dumString)
End If
'get the global variables
getOrgParam
dumString = App.Path & "\" & viniMain
'If Not ADOOpenConnection(dumstring, 3, 1) Then
'FOR SQL
If Not ADOOpenConnection(dumString, vServerType, 1) Then
MsgBox ("Error connecting to <sql server> database " & glob.serverdatabase & "@" & glob.dataservername)
GoTo ServerOffLine
End
End If

'12/11/2003 mat
getSysParam
'xxxxxxxx


'SetListeningPort
dispglobaltoMDI
selectModules kPCPModule, vModStat
' MDIForm1.Show
'10/21/2003 mat
' PostPCP

'Form1.Show
Exit Sub
ServerOffLine:
MDIForm1.mnuLogin.Enabled = False
MDIForm1.Show
End Sub

The system uses two types of database

SQL 2000 and an ms access MDB. i dont know what to change since its a code level work.

If you are using an Access database and that database is being installed under program files then that is most likely your problem. That will work on a basic XP system but will not work from Vista on up. The Database needs to be in a different location. Program Files is protected and will cause your mdb to be read only.

The place you showed it stop is not where it would actually stop the error would be occurring in the ADOOpenConnection routine you have an On Error in that routine that causes it to return false. When debugging one of the first steps is to locate the line where the error occurs. There are 3 ways to do this 2 of which I mentioned already the third is to set a breakpoint and step through the code line by line in the area you suspect a problem.

Edit: Looks like I was confused I said that I had mentioned two of the methods but it turns out I did that in a different thread.

Those two methods are
1: Set the IDE to break on all Errors - this will cause it to stop when an error occurs and not go to your error trap so you can see which line is causing a problem and what that actual error is
2: Comment out the on error in the troublesome routine so it stops when the error occurs

either of those will point you to the line and error message

Thanks for your prompt reply on this matter. I'd like to ask also if there is a different code / call on sql on windows 7 since it works absolutely fine on windows xp? actually the ms access is just a back up and not the database use for connecting the system. Its not what we use to login to the system. the error says cannot connect to sql.

I comment out the error and it log in. but no data is being displayed since its not connected to sql. i think its an sql name problem. is this what your looking for?

Option Explicit

Declare Function GetPrivatePathString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

Public rs(20) As New ADODB.Recordset
Public cn(99) As New ADODB.Connection
'Public database As New ADODB.Connection
Public cmdADO As New ADODB.Command
Public errLoop As ADODB.Error
Public kIniFile As String
Private INIFile As String
Public viniMain As String
Public BackupDrive As String
Public vLabelPrinter As String
Public vLabelSpread As Integer
Public vLabelPrintPort As String

do you have a dropbox or copy account? i can send you the project if its ok with you. (:

The code required is no different on 7 than it would be on XP.

What line does the error actually occur on?
What is the actual error message?
What connection string is being used at the time the error occurs?

The code you posted for the connection function is very hard to follow, no formatting, lots of commented lines and I have no idea what is being passed to it nor which cases are executing when you have your problem.

Try to answer the 3 questions above and you also should set a break point in the connection routine and step through it paying attention to which lines of code are being executed and which are being skipped.

Ill see what i can do thanks for your prompt reply. ill try to answer the 3 questions thanks much.

is there a way to install sql 7 on windows 7? or is there a compatibility issue on sql 7 database on windows 2000 sql?

I've not tried to install 7 on anything newer than XP, not much point in doing so.

The exact error is "Error connecting to <sql server> database IRISDB@IRISNET. Ill look for the exact line where the error occurs. i cant seem to get it right away since the errors pops when i login to the system. ill get back to you re that. thanks! (:

Hi DataMiser,

I think i already know the problem the system uses an ms access database for the login. for now thats my observation. Can i get specific instructions on how i can edit it?

That is not the actual error message it is something you are displaying from your code

Code:

---

MsgBox ("Error connecting to <sql server> database " & glob.serverdatabase & "@" & glob.dataservername)

---

You need to remove the on error in the connection routine to see what the error is and where it occurs. A simple comment character in front of that on error line will do it.

And yes if you are trying to connect to an Access database that is located in Program Files it will work under XP and not under Vista and later. If that is the case you simply need to move the database to a location which allows you to write to it such as Program Data and not use protected locations such as Program Files for data files.

how do i move the database? its actually inside a folder saved on Drive D. should i re install office 07? and save it somewhere else? is that what your trying to say?

No you do not need to re-install anything.

The database is simply a file you can move it as you would any other file. If it is not in a protected folder then you don't need to move it but if it is in the root folder, the system folder, program files folder or other protected folder then that could be your issue. You need to have the ability to have write access to the mdb. If the file is read only or is in a protected [read only] location then it may fail to open.

Its not in a protected folder. the read only is not ticked aswell. Still cant get it to connect. Damn System wont work.

is there something wrong with this connection string?

DProvider = "Server=" & glob.dataservername & ";Database=" & glob.serverdatabase & ";UID=" & glob.dataserveruid & ";"

Code:

---

Public Sub getSysParam()
On Error GoTo errSub
Dim rstTemp As New ADODB.Recordset
Dim sqlString As String
'sql 2

sqlString = "SELECT t_Sysparam.* FROM t_Sysparam;"

rstTemp.Open sqlString, cn(1), 3, 3
With rstTemp
    glob.Costing = IIf(IsNull(!f_Costing), "", !f_Costing)
    glob.RoundedTo = IIf(IsNull(!f_RoundedTo), "", !f_RoundedTo)
End With
Exit Sub
errSub:
MsgBox (Err.Description & " - GetOrgParam")
Resume Next
End Sub

---

I did what you intructed and i got this error message with the code above. It pushes thru but got this error message before the login form.

Attachment 128813 this is the error message after i put on a comment on the first error that appears which is the error that it cant connect to sql.

I can't really read that from the image.

Don't do a full screen shot, best to simply post the text of the error message or if you feel you must do a screen shot do just the error message and not the whole screen.

Quote:

---

Originally Posted by RSA2015

DProvider = "Server=" & glob.dataservername & ";Database=" & glob.serverdatabase & ";UID=" & glob.dataserveruid & ";"

---

There is no provider listed so I would expect it to fail

sorry sir. i dont get it sir. that code works fine on windows xp. can you give me a tip on what line or word should i edit? really need an expert advise on this matter. thanks.

Code:

---

Public Function ADOOpenConnection(ByVal database As String, _
                                  ByVal FileType As Integer, _
                                  Index As Integer)

'  This function serves as a generial tool for opening a database with
'  the ffg. extensions *.mdb,*.dbf. and sql files

Dim DProvider As String
On Error GoTo errmsg
Const DBFFiles = 0
Const MDBFiles = 1
Const SQLFiles = 3
Const MDB2000Files = 4
Const RemoteMDB = 5
'05/26/2000 NES This is TO Filter SQL Server to MSAccess 2000
    '02/11/2004 MAT
    'If FileType = SQLFiles Then FileType = MDB2000Files
    'XXXXX
Dim DatabaseName As String
Dim servername As String
Select Case FileType
            Case RemoteMDB
                    'DProvider = "Provider=MS Remote;"
                  ' DProvider = DProvider & "Data Source=" & glob.webDSN & ";"
                  ' DProvider = DProvider & "Remote Server=" & glob.URL
                  DProvider = "Provider=MS Remote;Remote Server=" & glob.URL
                  DProvider = ";Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob.webDSN
                   
          Case MDB2000Files
                      DProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
                      DProvider = DProvider & "Persist Security Info=False;"
                      DProvider = DProvider & "Data Source=" & glob.serverdatabase

          Case SQLFiles
                        'for SQL Server connection
                        'DProvider = "SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MCHD;data source=RSAWebServer" + ";UID=carlos;pwd=cty123"
                        'DProvider = "Server=RSAWebServer;Database=MCHD;UID=carlos;PWD=cty123;"
                        'DProvider = "Server=" & glob.dataservername & ";Database=" & glob.serverdatabase & ";UID=" & glob.dataserveruid & ";PWD=" & glob.dataserverpassword & ";"
                        'DProvider = "Server=" & glob.dataservername & ";Database=" & glob.serverdatabase & ";UID=" & glob.dataserveruid & ";"
                        'DProvider = DProvider & ";BackgroundFetch=Yes;"
          Case DBFFiles
                      DProvider = "Provider=MSDASQL.1;Persist Security Info=False;"
                      'DProvider = DProvider & "Extended Properties="
                      'DProvider = DProvider & """" & "DSN=dBase Files - Word;UID=;"
                      'DProvider = DProvider & "SourceDB=" & database & ";SourceType=DBF;"
                      'DProvider = DProvider & "Exclusive=No;Null=No"
                      'DProvider = DProvider & "Exclusive=No;BackgroundFetch=Yes;"
                      'DProvider = DProvider & "Collate=Machine;Null=Yes;Deleted=Yes;"
                      'DProvider = DProvider & """"
          'Case MDB2000Files
                      'DProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
                      'DProvider = DProvider & "Persist Security Info=False;"
                      'DProvider = DProvider & "Data Source=" & database
         
          Case Else
                      'DProvider = "Provider=Microsoft.Jet.OLEDB.3.51;"
                      'DProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
                      'DProvider = DProvider & "Persist Security Info=False;"
                     
                      'mat 05312007
                      'DProvider = DProvider & "Jet OLEDB:Database Password=" & vDBPassword & ";"
                     
                      'DProvider = DProvider & "Data Source=" & database
                      'DProvider = DProvider & ";BackgroundFetch=Yes;"
                   
End Select

Set cn(Index) = New ADODB.Connection
Select Case FileType
          Case SQLFiles
                  With cn(Index)
                            .Provider = "sqloledb"
                            .CommandTimeout = 0
                            .Open DProvider
                  End With
          Case Else
                  With cn(Index)
                          .ConnectionString = DProvider
                          .CommandTimeout = 30
                          .Open
                  End With
End Select

 ADOOpenConnection = True
 Exit Function
errmsg:
ADOOpenConnection = False
Exit Function
End Function

---

Sir can you check this code? i think this is the function to connect to access and sql DB. thanks.

Start by telling me the actual error message and which line it occurs on.
Also need to know what DProvider is equal to when it gets to the open statement.

If this does not get you past your problem then you should step through the code in that routine and see which lines are executing