Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: ODBC connection to SQL server DB

    I'm writing a VB app to read and write data to an SQL server database.

    I have successfully done so using ADO however I now need to do it using ODBC.

    I have set up the appropriate DSN and have no trouble reading data from the database but when I try to write to it I get a message indicating that the database is open for read only access.

    I'm no sure whether the restriction is and the VB, ODBC, or database level.

    Here's the code which opens the database and recordset.

    Set dbsWarehouseServer = OpenDatabase(ODBCDSName, _
    dbDriverNoPrompt, False, _
    "DSN=" & ODBCDSName)

    Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", dbOpenDynaset)

    Any help is greatly appreciated.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Locked records

    What if you changed this to your statement:

    Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", adOpenDynamic, adLockOptimistic)

    Just a thought.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2005
    Posts
    5
    Thank's for the idea. No luck though.
    Not sure but maybe the restriction is at the ODBC or SQL level although I have no trouble with ADO so I suspect ODBC.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Another Idea

    Just wondering...is RailID an integer value?

    If so, wouldn't you use:

    Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = " & RailID & "", dbOpenDynaset)

    instead of:

    Set rstRailSet = dbsWarehouseServer.OpenRecordset("Select Store_date, SLN From " & TBName & " where Rail_set_ID = '" & RailID & "'", dbOpenDynaset)

    Again, just another thought but probably not on the right path.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Smile

    When you create the ODBC connection on your pc, which username are you using, I believe that is not 'sa'. You must be using another username created in the SQL server user login.
    Check the right for that username you have created. Does it have the right to write to that specify database. If you put it to db owner, you can do anything with that database.

Posting Permissions

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