Combobox to select different catalogue in SQL

[dboone25]

Good afternoon everyone.


Having a problem trying to get my head around understanding if you can connect to the same database but select a different catalogue using a ComboBox1.


I have a CommandButton_Click() that when the user selects this this will make the connection to the right catalogue that has been selected which is Car1. This then outputs the sql query to the worksheet.

Private Sub CommandButton1_Click()

        'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset


        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset


        'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DATABSE1;Initial Catalog=CAR_Info;Integrated Security=SSPI;"
        
        objMyConn.ConnectionTimeout = 0
        objMyConn.CommandTimeout = 0
        
        objMyConn.Open
        
        'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = " SELECT * from SQLDATASET " 


        objMyCmd.CommandType = adCmdText


        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open


        'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset


End Sub

The first catalogue is called CAR1 and the second catalogue is called CAR2 connecting to the same databse called DATABASE1.

Private Sub UserForm_Initialize()



With Me.ComboBox1
    .AddItem "Car1"
    .AddItem "Car2"
End With




End Sub

Is there a way that when the user selects car2 using the combobox and then presses the command button this will connect to the correct catalogue? Can a warning text box display a message to say that the wrong catalogue has been selected?

Any help would be appreciated.

 

[Norie]

Did you try what I suggested in my first post?

 

[dboone25]

Yes, I have it exactly as you mentioned in your first post.

I have the connection string and have the initial catalogue connecting to Combobox1.Value.

Seems odd, connecting to one server name, one databse with the ComboBox.value in

CommandButton1_Click()

 

[dboone25]

If I place the connection string into ComboBox1_Change() this works fine and adds the requested data onto Sheet1 where as if I place the connection string into CommandButton1() I get the previous error I mentioned....so have eliminated that there may be a connection issue:

Just to check:

Private Sub UserForm_Initialize()



With Me.ComboBox1
    .AddItem "Car1"
    .AddItem "Car2"
End With






End Sub

Private Sub CommandButton1_Click()

        'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset


        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset


        'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=Vehicle_Info;Initial Catalog=Car1 & ComboBox1.Value ;Integrated Security=SSPI;"
        
        objMyConn.ConnectionTimeout = 0
        objMyConn.CommandTimeout = 0
        
        objMyConn.Open
        
        'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = " SELECT * From Dataset "


        objMyCmd.CommandType = adCmdText


        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open


        'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset

The only thing that is different is that I have used different names for the database and the server...these are just made-up names for ease of use otherwise I have reverted back to the start again...

 

[dboone25]

After running through everything still get this Run-time error Multiple-step OLE DB operation generated errors.Check each OLE DB status value, if available.No work was done....I have had a look at this post https://www.mrexcel.com/forum/excel-questions/681740-multiple-step-ole-db-operation-error.html but could not see what is causing the problem..

I have tried changing the Security "SSPI" to "True" and then "False"

Could this duplicated ADODD.Connection be the cause?


Has anyone else had a similar issue that they have resolved?

 

[Norie]

I'm sorry but this connection string is just wrong.

objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=Vehicle_Info;Initial Catalog=Car1 & ComboBox1.Value ;Integrated Security=SSPI;"

It's actually telling ADO that you want to use a database named 'Car1 & ComboBox1.Value' as the initial catalog.

 

[dboone25]

Whats the best way around this..as i have tried your original connection string you posted :

objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DATABASE;Initial Catalog=" & ComboBox1.Value & " ;Integrated Security=SSPI;"

I tried what you sent and that comes up with an error----Cannot open database "DATABASE" requested by the login. The login failed.


Is this correct?

 

[Norie]

When you get that error is what is selected in the combobox a valid name of a database on the server?

 

[dboone25]

thank you Norie..I was just about to ask the same question..this works..thank you for your help. Just need help loading .txt file into TextBox then all is perfecto...many thanks again!!