If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Connect from VB6.0 via ODBCDirect to SQL Server

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Posts: 78
Connect from VB6.0 via ODBCDirect to SQL Server

Hi All,
I have an existing application with frontend in VB 6.0 and backend in MS Access. I am trying to convert the application to SQL Server 2000 (standard) backend. At the moment the frontend uses ODBCDirect to communicate with MS Access backend. I upsized the MS Access database to SQL Server 2000 using MS Access upsizing wizard. (I know you need to use DTS, but I tried that and it did not copy the data across citing the error "The Server could not load DCOM", hence I used the upsizing wizard).
The backend now set up in SQL Server, I presume all I need to do is change the connection string in VB6.0, which I did, but I have not been able to make a successful connection yet. The following is the connection string I am using:

"ODBC;DSN=;Server=sql-server;Database=cabinetSQL;UID=dbo;PWD=;"

The SQL Server has been configured for windows authentication and the new database that I created due to upsizing, in the User section, it lists "dbo" as user name and login name is "domain/myloginname". So in the user ID I am using "dbo". I got some hints about how to connecte via ODBCDirect to SQL Server using the required workspace, as follows:

Set dbw = CreateWorkspace("pomsws", "admin", "", dbUseODBC)
Workspaces.Append dbw

Set pomsc = dbw.OpenConnection("pompom", dbDriverNoPrompt, _
true, con_string)

where con_string is the above mentioned connection string.

Can anybody let me know what I am doing wrong and provide any suggestions?

Thanks to all for your time and efforts.

Regards:
Prathmesh
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Posts: 78
An update on my question. I created a seperate small application just to test for the connectivity. The following is the code:

Dim strConnectionAdmin As String
Dim objConnAdmin As Connection
Dim CollegeRs As Recordset
Dim strsql As String
Dim i As Integer
Dim mdbodbcdirect As Database
Dim dbw As Workspace
Dim pomsc As Connection

strConnectionAdmin = "driver={SQL Server};Server=sql-server;Database=cabinetSQL;uid=;pwd=;"

Set dbw = CreateWorkspace("pomsws", "", "", dbUseODBC)
Workspaces.Append dbw

'On Error GoTo handler
Set pomsc = dbw.OpenConnection("pompom", dbDriverNoPrompt, _
False, strConnectionAdmin)

MsgBox "Connection success"

End Sub

This gives me an error "Invalid Argument" and points to the "Set pomsc" statement.

If I change the connection string to:
strConnectionAdmin = "ODBC;DSN=' ';driver={SQL Server};Server=sql-server;Database=cabinetSQL;uid=;pwd=;"

then it gives me an error for "ODBC-Call failed".

I am not sure where I am going wrong. Please help.

Regards:
Prathmesh
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On