Unsure how to successfully Connect and Insert to MS SQL 2008

[Tardisgx]

So with the generic code below I am successfully connecting to my database.

Sub conn()
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "SERVERNAME" ' Enter your server name here
    Database_Name = "DATABASENAME" ' Enter your database name here
    User_ID = "USERID" ' enter your user ID here
    Password = "PASSWORD" ' Enter your password here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    Cn.Close
End Sub

I pasted out the connection sample code in the unit test and edited it and the "SQLDATABASE" Class module; adding the relevant variables (database name & server name) and sValues and the Private Function MakeConnectionString concatenation was also edited.

sub connection try()
    Dim MyDatabase As SQLDatabase
    Set MyDatabase = Create_SQLDatabase()
    Dim MyRecordset As New SQLTestRecordset
    Dim MyConnection As New SQLTestConnection
    With MyDatabase
        .DSN = "mydsn"
        .DBType = "mssql"
        .Password = "Pa$$word"
        .Username = "myusername"
        Set .Recordset = MyRecordset
        Set .Connection = MyConnection
    End With

    Dim MyInsert As SQLInsert
    Set MyInsert = Create_SQLInsert
    MyInsert.Table = "new_import"

'Set The Fields
MyInsert.Fields = Array("broker_Name", "Broker_ref")

'Set the Values
Dim Values2D As Variant
Values2D = Array("'TEST1'", "'Test2'")

MyInsert.Values = Values2D

'Execute the query
MyDatabase.Execute MyInsert
End Sub

I could step through the sub until the end no errors stated. But the sql table didn't have a new row added. The no errors stated also worrying because when I purposely put errors in the connection details (server name ect) it still stepped through. I think I must be missing something obvious. Admittedly I only wrote 2 fields out, the table is meant to take house more.

[Beakerboy]

You are using the SQLTestRecordset and SQLTestConnection. These objects are stubs for the actual object and are to be used only when running the unit tests. Their purpose is to isolate the test connection from an actual database so the tests can be run when there is no actual database.

It looks like you have several different connection parameters than I have the library set up to use. Instead of using a DSN, you are specifying the driver, server, and database. I think I should change the connection string to either a Dictionary of keys and values, or two arrays to allow more flexibility in the connection configuration.

MyDatabase As SQLDatabase
Set MyDatabase = Create_SQLDatabase

You shouldn’t have to specify the Connection or Recordset.

I’ll add a sub called AddConnectionParameter to allow you to do this:

With MyDatabase
    .AddConnectionParameter “Driver”, “{SQL Server}”
    .AddConnectionParameter "Server”,  Server_Name
    .AddConnectionParameter "Database", Database_Name
End With

[Tardisgx]

Rejoice! I received a connection error instead of nothing happening. (I am not on my SQLs network so I wanted to see that). I believe it was me messing with/deleting/ not checking what this variable did .DBType = "mssql" and maybe the record and conection test dims I accidentally included ;S

And now I see your reply. I can't test the insert working or not until tomorrow but fingers crossed. Unfortunately I went through so many different versions today; what I actually pasted above was just not accurate to me. I do not think I am specifying a driver (I think i re-purposed that variable to the server name. Below is what I have now after I took a break and started from the beginning.

Sub newerconnection()
    Dim MyDatabase As SQLDatabase
    Set MyDatabase = Create_SQLDatabase()
    With MyDatabase
        .DatabaseName = "NAME"
        .UserName = "NAME"
        .Password = "PASSWORD"
        .ServerName = "NAME"
        .DBType = "mssql"
    End With

In SQLdatabase

'*********************************VARIABLES************************************
Private sDSN As String          'Server
Private sDatab As String        'Database_Name
Private sUid As String          'User Name
Private sPwd As String          'Password
Private cnt As iSQLConnection   'Connection
Private rst As iSQLRecordset    'Recordset
Private sType As String         'Type of Database (psql, mssql, mysql)

'***************************LET PROPERTIES*************************************

Public Property Let ServerName(sValue As String)
    sDSN = sValue
End Property
Public Property Let DatabaseName(sValue As String)
    sDatab = sValue
End Property

Public Property Let DBType(sValue As String)
    sType = sValue
End Property

Public Property Let Username(sValue As String)
    sUid = sValue
End Property

Public Property Let Password(sValue As String)
    sPwd = sValue
End Property

'**********************************METHODS*************************************

Private Function MakeConnectionString() As String
    MakeConnectionString = "Driver={SQL Server};Server=" & sDSN & ";Database=" & sDatab & _
    ";Uid=" & sUid & ";Pwd=" & sPwd & ";"
End Function

[Beakerboy]

i added some more details to my response...

[Beakerboy]

What you just did (modifying the database object) should work perfectly fine for your situation. Let me know if you have any more problems.

[Tardisgx]

Confirmed the insert worked. Thanks :)