Hi,
Recently started using VBA (Excel 2007) at work so not an expert! My issue is I'm connecting to SQL Server through the VBA below to select data from a database using an ODBC link on my computer. The code below works fine accept for the fact I have to hard code the database I am using into the SQL select. The server I am connecting to has many databases stored on it but say 5 of them are used regularly. The table structure in all the databases is identical but the records in these tables is different. Ideally I would like it so that when the end user is prompted to select the database the SQL code below points at that. At the moment it just points at one database, which might not be required every time. Ideally I would like to declare the database name as a variable from the ODBC connection and insert that in my SQL code so which ever database is chosen the variable will pick it up. However, I dont know how to do this and I have been searching the web for quite a long time trying to find something.
I'm obviousy happy to look at alternatives that do the same thing. Sorry for the very long introduction! And thank you in advance for any help.
See VBA code below:
'Setting the network ODBC connections
Dim sConn As String
Dim oQt As QueryTable
Dim sSql As String
Dim myCriteria As String
myCriteriaAcc = Trim(Range("C4").Value)
myCriteriaPeril = Trim(Range("C5").Value)
Info = MsgBox("Select EDM", vbOKOnly, "Instructions")
ScreenUpdating = False
'defining the connection string
sConn = "ODBC;DSN=;UID=;PWD=;APP=Microsoft Office 2007;"
sConn = sConn & "WSID=;DATABASE=;Network=DBMSSOCN"
sSql = "select a.ACCGRPNUM, a.ACCGRPNAME, sum(valueamt)TIV " & "from OM_EDM_Quote.dbo.accgrp a " & "inner join OM_EDM_Quote.dbo.loc b " & "on a.accgrpid = b.accgrpid " & "inner join OM_EDM_Quote.dbo.loccvg c " & Chr(13) & _
"on b.locid = c.locid " & "where a.ACCGRPNUM = '" & myCriteriaAcc & "'" & "and peril = '" & myCriteriaPeril & "'" & "Group by a.ACCGRPNUM, a.ACCGRPNAME"
Sheets("Sheet1").Columns("E:G").Select
Selection.ClearContents
Range("A1").Select
Set oQt = Sheet1.QueryTables.Add( _
Connection:=sConn, _
Destination:=Sheet1.Range("E4"), _
Sql:=sSql)
With oQt
.Name = "TIV_SUM"
.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
ScreenUpdating = True
End Sub
Recently started using VBA (Excel 2007) at work so not an expert! My issue is I'm connecting to SQL Server through the VBA below to select data from a database using an ODBC link on my computer. The code below works fine accept for the fact I have to hard code the database I am using into the SQL select. The server I am connecting to has many databases stored on it but say 5 of them are used regularly. The table structure in all the databases is identical but the records in these tables is different. Ideally I would like it so that when the end user is prompted to select the database the SQL code below points at that. At the moment it just points at one database, which might not be required every time. Ideally I would like to declare the database name as a variable from the ODBC connection and insert that in my SQL code so which ever database is chosen the variable will pick it up. However, I dont know how to do this and I have been searching the web for quite a long time trying to find something.
I'm obviousy happy to look at alternatives that do the same thing. Sorry for the very long introduction! And thank you in advance for any help.
See VBA code below:
'Setting the network ODBC connections
Dim sConn As String
Dim oQt As QueryTable
Dim sSql As String
Dim myCriteria As String
myCriteriaAcc = Trim(Range("C4").Value)
myCriteriaPeril = Trim(Range("C5").Value)
Info = MsgBox("Select EDM", vbOKOnly, "Instructions")
ScreenUpdating = False
'defining the connection string
sConn = "ODBC;DSN=;UID=;PWD=;APP=Microsoft Office 2007;"
sConn = sConn & "WSID=;DATABASE=;Network=DBMSSOCN"
sSql = "select a.ACCGRPNUM, a.ACCGRPNAME, sum(valueamt)TIV " & "from OM_EDM_Quote.dbo.accgrp a " & "inner join OM_EDM_Quote.dbo.loc b " & "on a.accgrpid = b.accgrpid " & "inner join OM_EDM_Quote.dbo.loccvg c " & Chr(13) & _
"on b.locid = c.locid " & "where a.ACCGRPNUM = '" & myCriteriaAcc & "'" & "and peril = '" & myCriteriaPeril & "'" & "Group by a.ACCGRPNUM, a.ACCGRPNAME"
Sheets("Sheet1").Columns("E:G").Select
Selection.ClearContents
Range("A1").Select
Set oQt = Sheet1.QueryTables.Add( _
Connection:=sConn, _
Destination:=Sheet1.Range("E4"), _
Sql:=sSql)
With oQt
.Name = "TIV_SUM"
.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
ScreenUpdating = True
End Sub
