Object variable or With block variable not set.

Question

I'm trying to connect SQL Server to Access. I have the connection function in a module. I call this function in another module.

Here is the code in my module:

'Variabel voor SQL Server Connectie
Public SQLConnectie As ADODB.Connection

'Connecten met SQL Server
Public Function DBConn() As ADODB.Connection
If Not (SQLConnectie Is Nothing) Then
    Set SQLConnectie = New ADODB.Connection
    With SQLConnectie
        .CommandTimeout = 15
        .Mode = adModeReadWrite
        .ConnectionString = "Provider=SQLNCLI11;Server=dafehvmvdsql3;Database=PROVOMotorenfabriek;Integrated Security=SSPI; Persist Security Info=False"
        .Open
    End With
End If

Set DBConn = SQLConnectie
Set SQLConnectie = Nothing
End Function

And below the code in the module which executes the stored procedure in SQL Server:

Call DBConn.Execute("EXEC spStoringToevoegen " & productielijnMW & ", " & Forms(Formnaam)!cbLijngedeelte & ".............etc

I get the error: Object variable or With block variable not set. Every answer I find says I need to put set in front of some variables but I can't find which one this should be.

Thanks in advance,

My VBA is rather rusty, but in this code you are saying if NOT (SQLConnectie is a NULL value), no? Since it is a NULL value initially it would always be a NULL value which would set SQLConnectie to NULL as well.AND ALSO, it is great that this code didn't work and prevented you from using SQL commands like this. You should use parameters (check SQL injections attack). — Cetin Basoz, Jul 03 '18 at 11:09
Cetin likely is right. This code also contains two major design flaws: 1. A connection can be invalid, and still not be nothing. A closed connection, for example, is not nothing. You shouldn't persistently store a connection, and if you do, you should add more checks. 2. Your code is wide open to SQL injection. Even if all users are trusted, that can still lead to unexpected errors. — Erik A, Jul 03 '18 at 11:16
@CetinBasoz do you mean like this? .Parameters.Append .CreateParameter("Productielijn", adTinyInt, adParamInput, , productielijnMW) — Dennis vd Eijnde, Jul 03 '18 at 11:18
Could you guys give me some hints on how to implement this better? I'm a beginner with connections to SQL Server — Dennis vd Eijnde, Jul 03 '18 at 11:19
Just scrap the `If` condition, and don't declare `SQLConnectie` as a global for the first problem. For the second problem, use [parameters](https://stackoverflow.com/a/49509616/7296893) (which means creating an `ADODB.Command` object, and not just calling `.Execute`, bottom section is about ADODB). — Erik A, Jul 03 '18 at 11:26
@ErikvonAsmuth Tnx Erik. I'm not much of a safety guy....But I understand what you mean. — Dennis vd Eijnde, Jul 03 '18 at 11:33

score 1 · Accepted Answer · answered Jul 03 '18 at 12:06

Adding this to sample parameters.

As I said my VBA is very rusty (and I find VBA particularly weird language to work with, add Access to that it sounds like a misery to me). It would be much easier if you used some other backend (and language as well). Anyway, here is a sample with parameters in VBA (Excel):

Sub Macro1()
   Dim oRecordset1 As ADODB.Recordset
   Dim oConnection As ADODB.Connection
   Dim oCommand As ADODB.Command
   Dim oParameter1 As ADODB.Parameter
   Dim oParameter2 As ADODB.Parameter


  Set oConnection = New ADODB.Connection
  Set oCommand = New ADODB.Command

  oConnection.ConnectionString = "Provider=SQLNCLI11.0;Data Source=.\SQLExpress;Trusted_connection=Yes"

  oConnection.Open
  oCommand.ActiveConnection = oConnection
  oCommand.CommandType = 4
  oCommand.CommandText = "Northwind.dbo.[CustomersSelectLike]"

  Set oParameter1 = oCommand.CreateParameter("@country", 130, 1, -1) ' adWChar
  oCommand.Parameters.Append oParameter1
  oCommand.Parameters("@country").Value = "USA"

  Set oParameter2 = oCommand.CreateParameter("@customer", 130, 1, -1)
  oCommand.Parameters.Append oParameter2
  oCommand.Parameters("@customer").Value = "%"

  Set oRecordset = oCommand.Execute()
  Sheet1.Range("A1").CopyFromRecordset (oRecordset)
End Sub

Object variable or With block variable not set.

1 Answers1