Connecting excel to SQL via VB

[excel_2009]

Hi excel gurus,

can anyone point me in the right direction? i want to be able to load up an excel file and allow a user to connect to a database using vb, is that possible? if so how?

Thank you

 

[Ruddles]

I use this for the Oracle database where I work:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub RunSQLqueryOracleDB()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim rsConn As ADODB.Connection
  Dim strConnect As String
  Dim rsData As ADODB.Recordset
  Dim strSQL As String  [/FONT]
[FONT=Fixedsys]
  Set rsConn = New ADODB.Connection

  strConnect = "DRIVER={[COLOR=red]Oracle in Ora9[/COLOR]};SERVER=[COLOR=red]servername[/COLOR];DBQ=[COLOR=red]databasename[/COLOR];UID=[COLOR=red]userid[/COLOR];PWD=[COLOR=red]password[/COLOR];" _
             & "DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;" _
             & "GDE=F;FRL=Lo;BAM=IfAllSuccessful;MTS=F;MDI=Me;CSR=F;FWC=F;PFC=10;TLO=O;"[/FONT]
[FONT=Fixedsys]  rsConn.Open strConnect
  
  If rsConn.State = 1 Then
    MsgBox "Connection open" & Space(15), vbOKOnly + vbInformation
  Else
    MsgBox "Connection failed" & Space(15), vbOKOnly + vbExclamation
    Exit Sub
  End If
 
  strSQL = "SELECT * FROM [COLOR=red]tablename[/COLOR];"
  
  Set rsData = New ADODB.Recordset
  
  rsData.Open strSQL, rsConn, 3, 1, 1
  
  MsgBox rsData.RecordCount & " record" & IIf(rsData.RecordCount = 1, "", "s") _
       & " found" & Space(15), vbOKOnly + vbInformation
  
  rsData.Close
  
  Set rsData = Nothing
  Set rsConn = Nothing[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub[/FONT]

Change the bits in red to suit and then check all the remaining parameters in the connection string.

 

[excel_2009]

Thank you Ruddles, i'll adapt it and will let you know the outcomes

Thank you

 

[tatendatiffany]

ohh i am having similar issues trying to connect mysql in vba has it worked for you, might try similar approach

 

[tatendatiffany]

sorry again i hope you wont mind if i also post my question on your thread as they are kind of related. with my problem i am already connected to the database via (userform) now i just need to add 28 sql queries into the vba e.g.
"SELECT COUNT (INUNACPT) FROM AIS3AM4.KTPT80T WHERE CDPRODCO=RETAIL_MAP"

and display results on excel sheet but i am facing difficulty with my recordst could you please help me.

Public Sub Queries()
Dim rst As ADODB.Recordset
Dim rst1 As ADODB.Connection
Dim strSQL1 As String
strSQL1 = "SELECT COUNT (INUNACPT) FROM AIS3AM4.KTPT80T WHERE CDPRODCO=RETAIL_MAP"
With strSQL1
.Open strSQL1
Sheet1.Range("AC2").Activate
.Close
End With
End Sub

 

[Ruddles]

With [COLOR=red][B]rst[/B][/COLOR]

(I'm assuming you've already set up the connection to the database elsewhere in your code.)

And of course between .Open and .Close you need to do something with the recordset, like copy it to the worksheet perhaps.