Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2003
    Posts
    1

    Exclamation Unanswered: SQL DMO and Visual Basic Six ...

    Hi,

    I really need some help with the following code. I have created a project in VB6 and referenced ADO 2.7 and MS SQLDMO. I then entered the following code and everything worked fine ...


    Option Explicit

    '// SQL-DMO Objects
    Private oSQLApplication As SQLDMO.Application
    Private oSQLServer As SQLDMO.SQLServer
    Private oSQLDatabase As SQLDMO.Database
    Private oSQLLinkedServer As SQLDMO.LinkedServer

    'Private WithEvents oSQLServer As SQLDMO.SQLServer

    '// MS ADO Objects
    Private oADOConnection As ADODB.Connection
    Private oADORecordset As ADODB.Recordset

    '// Private Constants for Linked Datasource Access
    Private Const MSACCESS_PRODUCT_NAME As String = "Microsoft.Jet.OLEDB.4.0"
    Private Const ORACLE_PRODUCT_NAME As String = "."
    Private Const MSSQL_PRODUCT_NAME As String = "."
    Private Const ODBC_PRODUCT_NAME As String = "."

    '// Private Data Engine Variables
    Private IsConnected As Boolean

    Public Sub ListProviders()

    For Each oSQLLinkedServer In oSQLServer.LinkedServers
    With oSQLLinkedServer
    Debug.Print .Name
    Debug.Print .DataSource
    Debug.Print .ProviderName
    Debug.Print .ProductName
    Debug.Print ""
    End With
    Next

    End Sub

    Public Sub main()

    Set oSQLApplication = New SQLDMO.Application
    Set oSQLDatabase = New SQLDMO.Database
    Set oSQLServer = New SQLDMO.SQLServer

    IsConnected = False

    Connect
    ListProviders
    CreateNewDataSource
    Disconnect

    If IsConnected = True Then Disconnect

    Set oSQLApplication = Nothing
    Set oSQLDatabase = Nothing
    Set oSQLServer = Nothing

    End Sub


    Public Sub Connect()

    On Error GoTo ErrorTrap

    With oSQLServer
    .LoginTimeout = -1
    .LoginSecure = True
    .AutoReConnect = False
    .Connect "(local)"
    IsConnected = True
    End With

    Exit Sub

    ErrorTrap:
    '// Put error message here
    Debug.Print Err.Number & vbCrLf & Err.Description
    IsConnected = False

    End Sub

    Public Sub CreateNewDataSource()

    On Error GoTo ErrorTrap

    Set oSQLLinkedServer = New SQLDMO.LinkedServer

    If IsConnected = True Then
    With oSQLLinkedServer
    .Name = "Class_Test"
    .ProviderName = MSACCESS_PRODUCT_NAME
    .ProductName = MSACCESS_PRODUCT_NAME
    .DataSource = "D:\iBase\SQLLinkedTest.mdb"
    End With
    oSQLServer.LinkedServers.Add oSQLLinkedServer
    End If

    Set oSQLLinkedServer = Nothing

    Exit Sub

    ErrorTrap:
    Set oSQLLinkedServer = Nothing
    MsgBox Err.Description

    End Sub
    Public Sub Disconnect()

    On Error GoTo ErrorTrap

    oSQLServer.Disconnect
    IsConnected = False

    Exit Sub

    ErrorTrap:
    '// Put error message here
    Debug.Print Err.Number & vbCrLf & Err.Description
    IsConnected = True

    End Sub


    But, then I created an ActiveX DLL to put the code in so I could reuse it in some ASP applications (slight changes for a ActiveX)


    Option Explicit

    '// SQL-DMO Objects
    Private oSQLApplication As SQLDMO.Application
    Private oSQLDatabase As SQLDMO.Database
    Private oSQLLinkedServer As SQLDMO.LinkedServer

    Private WithEvents oSQLServer As SQLDMO.SQLServer

    '// MS ADO Objects
    Private oADOConnection As ADODB.Connection
    Private oADORecordset As ADODB.Recordset

    '// Private Constants for Linked Datasource Access
    Private Const MSACCESS_PRODUCT_NAME As String = "Microsoft.Jet.OLEDB.4.0"
    Private Const ORACLE_PRODUCT_NAME As String = "."
    Private Const MSSQL_PRODUCT_NAME As String = "."
    Private Const ODBC_PRODUCT_NAME As String = "."

    '// Private Data Engine Variables
    Private IsConnected As Boolean
    Public Sub Connect()

    On Error GoTo ErrorTrap

    With oSQLServer
    .LoginTimeout = -1
    .LoginSecure = True
    .AutoReConnect = False
    .Connect "(LOCAL)"
    End With

    IsConnected = True

    Exit Sub

    ErrorTrap:
    '// Put error message here
    Open "C:\Tmp.txt" For Append As #1
    Print #1, Err.Number & " - " & Err.Description
    Close #1
    Debug.Print Err.Number & vbCrLf & Err.Description
    IsConnected = False

    End Sub


    Public Sub CreateNewDataSource()

    On Error GoTo ErrorTrap

    Set oSQLLinkedServer = New SQLDMO.LinkedServer

    If IsConnected = True Then
    With oSQLLinkedServer
    .Name = "Class_Test"
    .ProviderName = MSACCESS_PRODUCT_NAME
    .ProductName = MSACCESS_PRODUCT_NAME
    .DataSource = "D:\iBase\SQLLinkedTest.mdb"
    End With
    oSQLServer.LinkedServers.Add oSQLLinkedServer
    End If

    Set oSQLLinkedServer = Nothing

    Exit Sub

    ErrorTrap:
    '// Put error message here
    Open "C:\Tmp.txt" For Append As #1
    Print #1, Err.Number & " - " & Err.Description
    Close #1
    Debug.Print Err.Number & vbCrLf & Err.Description
    Set oSQLLinkedServer = Nothing

    End Sub


    Public Sub Disconnect()

    On Error GoTo ErrorTrap

    oSQLServer.Disconnect
    IsConnected = False

    Exit Sub

    ErrorTrap:
    '// Put error message here
    Debug.Print Err.Number & vbCrLf & Err.Description
    IsConnected = True

    End Sub


    Private Sub Class_Initialize()

    Set oSQLApplication = New SQLDMO.Application
    Set oSQLDatabase = New SQLDMO.Database
    Set oSQLServer = New SQLDMO.SQLServer

    IsConnected = False

    End Sub

    Private Sub Class_Terminate()

    If IsConnected = True Then Disconnect

    Set oSQLApplication = Nothing
    Set oSQLDatabase = Nothing
    Set oSQLServer = Nothing

    End Sub


    but whenever I create the object and run the connect routine, I get the following error ...

    -2147221499 - [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION

    Can anyone help with this ???

    Cheers

    Mic
    Last edited by micpringle; 02-24-03 at 17:04.

Posting Permissions

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