Results 1 to 2 of 2
  1. #1
    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

  2. #2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •