micpringle
02-24-03, 17:02
| 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 |