Consultation d'un server SQL dans Excel avec une requête paramétrée à l'aide de VBA

J'essaie d'interroger une table dans Microsoft Excel à l'aide de VBA. J'ai écrit un code pour essayer d'accomplir cette tâche, mais je continue d'get une erreur d'exécution "1004" en disant que c'est une erreur ODBC générale. Je ne suis pas sûr de ce que je dois faire pour que ce code fonctionne correctement afin que je puisse interroger ce tableau.

J'utilise SQL Server Express, le server auquel je me connecte .\SQLEXPRESS

Base de données:

Databaselink

Consultation du tableau des produits Code VBA:

 Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Sub ParameterQueryExample () Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub '--- crée un ListObject-QueryTable sur Sheet1 qui utilise la valeur dans Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub 'Cell Z1 comme paramètre de ProductID pour une requête SQL Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub 'Une fois créé, la requête se rafraîchira lors des modifications apscopes à Z1. Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Dim sSQL As Ssortingng Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Dim qt As QueryTable Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Dim rDest As Range Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub '--build connection ssortingng - doit utiliser ODBC pour permettre des parameters Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Const sConnect = "ODBC;" Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub & _ Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub "Driver = {SQL Server Native Client 10.0};" Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub & _ Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub "Serveur =. \ SQLEXPRESS;" Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub & _ Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub "Base de données = TSQL2012;" Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub & _ Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub "Trusted_Connection = yes" Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub '- instruction SQL constructive Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub sSQL = "SELECT *" & _ Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub "FROM TSQL2012.Production.Products Produits" & _ Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub "WHERE Products.productid =?;" Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub '- Créer ListObject et get QueryTable Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Définir rDest = Feuilles ("Feuille1"). Plage ("A1") Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub rDest.CurrentRegion.Clear 'option-delete table existante Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Définir qt = rDest.Parent.ListObjects.Add (SourceType: = xlSrcExternal, _ Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Source: = Array (sConnect), Destination: = rDest) .QueryTable Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub '--add Parameter to QueryTable - utilisez la cellule Z1 comme paramètre Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Avec qt.Parameters.Add ("ProductID", xlParamTypeVarChar) Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub .SetParam xlRange, Sheets ("Sheet1"). Gamme ("Z1") Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub .RefreshOnChange = True Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub '-populate QueryTable Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Avec qt Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub .CommandText = sSQL Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub .CommandType = xlCmdSql Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub .AdjustColumnWidth = True 'ajoute d'autres propriétés de table ici Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub .BackgroundQuery = False Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Set qt = Nothing Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub Set rDest = Nothing Sub ParameterQueryExample() '---creates a ListObject-QueryTable on Sheet1 that uses the value in ' Cell Z1 as the ProductID Parameter for an SQL Query ' Once created, the query will refresh upon changes to Z1. Dim sSQL As Ssortingng Dim qt As QueryTable Dim rDest As Range '--build connection ssortingng-must use ODBC to allow parameters Const sConnect = "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=.\SQLEXPRESS;" & _ "Database=TSQL2012;" & _ "Trusted_Connection=yes" '--build SQL statement sSQL = "SELECT *" & _ " FROM TSQL2012.Production.Products Products" & _ " WHERE Products.productid = ?;" '--create ListObject and get QueryTable Set rDest = Sheets("Sheet1").Range("A1") rDest.CurrentRegion.Clear 'optional- delete existing table Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _ Source:=Array(sConnect), Destination:=rDest).QueryTable '--add Parameter to QueryTable-use Cell Z1 as parameter With qt.Parameters.Add("ProductID", xlParamTypeVarChar) .SetParam xlRange, Sheets("Sheet1").Range("Z1") .RefreshOnChange = True End With '--populate QueryTable With qt .CommandText = sSQL .CommandType = xlCmdSql .AdjustColumnWidth = True 'add any other table properties here .BackgroundQuery = False .Refresh End With Set qt = Nothing Set rDest = Nothing End Sub

J'ai trouvé cette question Stack Overflow avec une search Google. Il ne semble pas que quelqu'un ait essayé de répondre, alors voici ce que j'ai fini de faire. Au lieu d'utiliser "QueryTable", utilisez un object de command ADO comme fait dans cet article MSDN .

MSDN Exemple:

 Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Dim Conn1 As ADODB.Connection Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Dim Cmd1 Comme ADODB.Command Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Dim Param1 As ADODB.Parameter Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Dim Rs1 As ADODB.Recordset Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Dim i As Integer Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() 'Trap toute erreur / exception. Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Sur le résumé d'erreur Suivant Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() 'Créer et ouvrir un object de connection. Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Set Conn1 = New ADODB.Connection Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Conn1.ConnectionSsortingng = "DSN = Biblio; UID = admin; PWD =;" Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Conn1.Open Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() 'Créer un object de command. Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Set Cmd1 = Nouveau ADODB.Command Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Cmd1.ActiveConnection = Conn1 Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID <?" Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() 'Créer un object de paramètre. Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Set Param1 = Cmd1.CreateParameter (, adInteger, adParamInput, 5) Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Param1.Value = 5 Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Cmd1.Parameters.Append Param1 Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() Set Param1 = Nothing Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute() 'Ouvrir l'object Recordset. Dim Conn1 As ADODB.Connection Dim Cmd1 As ADODB.Command Dim Param1 As ADODB.Parameter Dim Rs1 As ADODB.Recordset Dim i As Integer ' Trap any error/exception. On Error Resume Next ' Create and Open Connection Object. Set Conn1 = New ADODB.Connection Conn1.ConnectionSsortingng = "DSN=Biblio;UID=admin;PWD=;" Conn1.Open ' Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = Conn1 Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?" ' Create Parameter Object. Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5) Param1.Value = 5 Cmd1.Parameters.Append Param1 Set Param1 = Nothing ' Open Recordset Object. Set Rs1 = Cmd1.Execute()