Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013

    Unanswered: runtime error 3611

    I have an intractable problem at work that I must get fixed within a week or so. Anyone that has VB skills might be able to help me.
    We have an Oracle Db that connects thru an ODBC to an MS Access 2003 Db. due the limitations of MS Access 2003, the precision length in Oracle is set to 38 and Access can only handle 10 digits. Thus, Access converts Number data types in Oracle to Text when the ODBC feed completes.

    I was able to change several columns back to Number in a standlone test Db with the SQL statement ALTER. But, to my shock and horror when I attempted to do this in the test Db with a linked table I get error 3611.

    There must be a way (hopefully in a DDL query in Access SQL) to force the linked table take modification of the column data type.

    Any code that is reusable which I can (with a little help) customize to fix this problem will be very much appreciated. Not a VB programmer so responses need to be detailed. SQL is my expertise.
    Last edited by robleh; 07-03-13 at 15:56.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    1. If you want to change the column definition on the server (provided that you have the permissons to do so), you can use a pass-through query. I don't work with Oracle, but here's how it's done with a SQL Server:
    Sub ChangeTableDefinition(ByVal Connection As String, ByVal TableName As String, ByVal ColumnName As String, ByVal Datatype As String)
        Const C_SQL As String = "ALTER TABLE @T ALTER COLUMN @C @Y;"
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        strSQL = Replace(Replace(Replace(C_SQL, "@T", TableName), "@C", ColumnName), "@Y", Datatype)
        Set qdf = CurrentDb.CreateQueryDef("")
        With qdf
            .Connect = Connection
            .SQL = strSQL
            .ReturnsRecords = False
        End With
        Set qdf = Nothing
    End Sub
    You can call it, using:
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=TestDB;Trusted_Connection=Yes;"
        Const c_Table As String = "Tbl_Filters"
        Const c_Column As String = "Modification_Date"
        Const c_Type As String = "DATETIME"
        ChangeTableDefinition c_Connect, c_Table, c_Column, c_Type
    2. If you want to change the table definition of an Access table, you can also use a DDL query (ALTER TABLE ALTER COLUMN...) however you should probably use an UPDATE query first to be sure that the conversion operated by the DDL query won't cause an overflow.
    Have a nice day!

Posting Permissions

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