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]

You should be able to simply put the value from the combobox into the connection string.

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

 

[dboone25]

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.

 

[xenou]

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.

 

[dboone25]

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'

 

[xenou]

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.

 

[dboone25]

Brilliant, thank you..ill post up in a short while to see if this is working.

 

[dboone25]

So I included the other initial catalogue and added in the other combobox value as seen below:

 [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:

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:

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

So I changed the

[/COLOR][COLOR=#333333]Integrated Security=SSPI[/COLOR][COLOR=#333333]

to

[/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.

 

[Norie]

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.

 

[dboone25]

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.