33216CLC
asked on
Connecting to an Sql Server Database using VB6 (DAO)
I've converted from using an access database to sql Server. I have always used DAO. I am aware that DAO has it's contraints, but for now will continue with it. I plan to move to ado gradually.
I am unable to connect to my database - Kept getting "ODBC call failed". Here is code I used:
[
Global wsPayroll As Workspace
Global dbPayroll As Connection
Global dbUser As Connection
Global dbs As Database
Global MyPath As String
Global MyDbPath As String
Global MyDbName As String
Global MyAppPath As String
Global s As String
Open App.Path & "\Payroll.txt" For Input As #1
Input #1, s
MyPath = Right(s, Len(s) - 3)
If Right(s, 1) <> "\" Then
MyPath = MyPath & "\"
End If
Input #1, s
MyAppPath = Right(s, Len(s) - 4)
If Right(s, 1) <> "\" Then
MyAppPath = MyAppPath & "\"
End If
Close #1
MyDbName = "Paysql_data"
MyDbPath = MyPath & MyDbName & ".mdf"
Set wsPayroll = CreateWorkspace("NewODBCWo
Set dbPayroll = wsPayroll.OpenConnection("
dbDriverNoPrompt, , _
"ODBC;DATABASE=Paysql_data
]
I get the error when executing the last line of code above.
Any help would be good.
Thnaks
I am unable to connect to my database - Kept getting "ODBC call failed". Here is code I used:
[
Global wsPayroll As Workspace
Global dbPayroll As Connection
Global dbUser As Connection
Global dbs As Database
Global MyPath As String
Global MyDbPath As String
Global MyDbName As String
Global MyAppPath As String
Global s As String
Open App.Path & "\Payroll.txt" For Input As #1
Input #1, s
MyPath = Right(s, Len(s) - 3)
If Right(s, 1) <> "\" Then
MyPath = MyPath & "\"
End If
Input #1, s
MyAppPath = Right(s, Len(s) - 4)
If Right(s, 1) <> "\" Then
MyAppPath = MyAppPath & "\"
End If
Close #1
MyDbName = "Paysql_data"
MyDbPath = MyPath & MyDbName & ".mdf"
Set wsPayroll = CreateWorkspace("NewODBCWo
Set dbPayroll = wsPayroll.OpenConnection("
dbDriverNoPrompt, , _
"ODBC;DATABASE=Paysql_data
]
I get the error when executing the last line of code above.
Any help would be good.
Thnaks
ASKER
Dhaest,
Thank you for the response. The code you gave works for ADO, but I'm using DAO. Do you have the code for making the connection with DAO? The one I use presently only works with An Access Database, but not sql Server. I'm using SQL Server 7.0.
Thank you for the response. The code you gave works for ADO, but I'm using DAO. Do you have the code for making the connection with DAO? The one I use presently only works with An Access Database, but not sql Server. I'm using SQL Server 7.0.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
set oConn as new adodb.connection
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword
See http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer for more information about connectionstrings