Combobox to select different catalogue in SQL

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
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.

Code:
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.

Code:
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
You should be able to simply put the value from the combobox into the connection string.
Code:
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DATABSE1;Initial Catalog=" & ComboBox1.Value  & ";Integrated Security=SSPI;"
 
Upvote 0
Fantastic, thanks..this does work but how do you get to connect via the command button as this automatically connects once say either Car1 or Car2 is selected.
 
Upvote 0
thanks..this does work but how do you get to connect via the command button as this automatically connects once say either Car1 or Car2 is selected.

Norie's code is meant for the command button, as in your example.

Can a warning text box display a message to say that the wrong catalogue has been selected?
You can warn the user if they have selected the wrong database by checking the value they have selected. But I don't know how you will know the user has chosen wrong. They only have two choices here, and if one is wrong then why even give the choice for it? The only real danger is that they make no selection at all, in which case you can either provide a default, abort, or give them what they deserve for not choosing properly.

I don't honestly think it matters. You don't have to use the initial catalogue after you have connected.
 
Last edited:
Upvote 0
Norie's code is meant for the command button, as in your example.


its located in the command button routine but when i run this and select the combobox value it auto connects to that database and outputs the sql query


You can warn the user if they have selected the wrong database by checking the value they have selected. But I don't know how you will know the user has chosen wrong. They only have two choices here, and if one is wrong then why even give the choice for it? The only real danger is that they make no selection at all, in which case you can either provide a default, abort, or give them what they deserve for not choosing properly.

I don't honestly think it matters. You don't have to use the initial catalogue after you have connected.


Ahh thanks for this..that makes a little more sense..just assummed you could say if 'Car1' is not found in Initial Catalogue a text box would apprea stating 'please try again'
 
Upvote 0
I think in these connection strings for SQL Server, Initial Catalog is the name of a database on the server. I don't know what happens if you provide a non-existent database name. You can just test that for yourself (i.e., just try Initial Catalog = "FOOBAR" and see what happens).


edit:

You can also try Initial Catalog =

(meaning, an empty string).

And see if that works.
 
Last edited:
Upvote 0
So I included the other initial catalogue and added in the other combobox value as seen below:

Code:
 [COLOR=#333333]'Open Connection'
[/COLOR][COLOR=#333333]        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DATABSE1 ;Initial Catalog=CAR_Info & [/COLOR][COLOR=#333333]Initial Catalog=CAR_Value & Combobox1.Car1 & Combobox1.Car2[/COLOR][COLOR=#333333] ;Integrated Security=SSPI;"[/COLOR]

It now comes up with an error:

Run-time error '-2147467259 (800004005)':

Cannot open databse "Initial Catalog=CAR_Info & Initial Catalog=CAR_Value & Combobox1.Car1 & Combobox1.Car2

Debug showing at:

Code:
objMyConn.Open



I tried taking out one of the databases CAR_Value and using one Combobox1.Car1...this throws a different error:

Run-time error Cannot open database "CAR_Info & ComboBox1.Car1" requested by the login. The login failed.


Debug shgowing at:

Code:
[/COLOR][COLOR=#333333]objMyConn.Open[/COLOR][COLOR=#333333]


So I changed the
Code:
[/COLOR][COLOR=#333333]Integrated Security=SSPI[/COLOR][COLOR=#333333]
to
Code:
[/COLOR][COLOR=#333333]Integrated Security=True[/COLOR][COLOR=#333333]
...this then shows another error:


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 google search and looks like it might be using ADO.NET or something..not quite sure...can anyone help me with this please.
 
Upvote 0
What exactly are you trying to do?

That connection string, in particular the Initial Catalog part, doesn't make sense.

For one thing I'm pretty sure you can only specify one database on the server as the initial catalog.

Also, that's not how you would use the value from a combobox in the connection string.
 
Last edited:
Upvote 0
Trying to get ComboBox1 to select Car1 and use the CommandButton1 to connect to the database.


i.e When the user selected the combobox value and then selects the command button the output will run the SQL script onto sheet1.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top