1. <i id='S4GlE'><tr id='S4GlE'><dt id='S4GlE'><q id='S4GlE'><span id='S4GlE'><b id='S4GlE'><form id='S4GlE'><ins id='S4GlE'></ins><ul id='S4GlE'></ul><sub id='S4GlE'></sub></form><legend id='S4GlE'></legend><bdo id='S4GlE'><pre id='S4GlE'><center id='S4GlE'></center></pre></bdo></b><th id='S4GlE'></th></span></q></dt></tr></i><div id='S4GlE'><tfoot id='S4GlE'></tfoot><dl id='S4GlE'><fieldset id='S4GlE'></fieldset></dl></div>

      <small id='S4GlE'></small><noframes id='S4GlE'>

      <tfoot id='S4GlE'></tfoot>

    2. <legend id='S4GlE'><style id='S4GlE'><dir id='S4GlE'><q id='S4GlE'></q></dir></style></legend>
        <bdo id='S4GlE'></bdo><ul id='S4GlE'></ul>

      在VBA中从访问模块传递参数时调用存储过程

      Calling Stored Procedure while passing parameters from Access Module in VBA(在VBA中从访问模块传递参数时调用存储过程)

        <tbody id='UUXNk'></tbody>
      <tfoot id='UUXNk'></tfoot>
      <legend id='UUXNk'><style id='UUXNk'><dir id='UUXNk'><q id='UUXNk'></q></dir></style></legend>

      <small id='UUXNk'></small><noframes id='UUXNk'>

          <bdo id='UUXNk'></bdo><ul id='UUXNk'></ul>

          1. <i id='UUXNk'><tr id='UUXNk'><dt id='UUXNk'><q id='UUXNk'><span id='UUXNk'><b id='UUXNk'><form id='UUXNk'><ins id='UUXNk'></ins><ul id='UUXNk'></ul><sub id='UUXNk'></sub></form><legend id='UUXNk'></legend><bdo id='UUXNk'><pre id='UUXNk'><center id='UUXNk'></center></pre></bdo></b><th id='UUXNk'></th></span></q></dt></tr></i><div id='UUXNk'><tfoot id='UUXNk'></tfoot><dl id='UUXNk'><fieldset id='UUXNk'></fieldset></dl></div>
              • 本文介绍了在VBA中从访问模块传递参数时调用存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我在 Access 2010 中使用 Microsoft SQL Server 2008 后端.我有一个将新值(由参数提供)插入表中的存储过程.分配给参数的值是从文件夹中存储的文件中获取的.Windows 文件系统用于扫描特定文件夹以制作其中的文件列表.对于每个扫描的文件,调用存储过程,并使用 FileName 和 QueueId(不带扩展名的文件名)以及其他值作为调用的存储过程的参数.存储过程用于为表的每个文件创建新记录.

                I am working in Access 2010 with a Microsoft SQL Server 2008 backend. I have a stored procedure that inserts new values(supplied by the parameters) into a table. The values assigned to the parameters are obtained from files stored in a folder. The Windows File System is used to scan a particular folder to make a list of the files in it. For each scanned file the stored procedure is called and the FileName and QueueId (Filename without extension) along with other values are used as parameters for the stored procedure called. The stored procedure is used to create new records for each file for a table.

                Public Function CreateInstrumentInterfaceLogRecords(BatchID As Long, InstrumentName As            String) As Boolean
                On Error GoTo HandleError
                
                Dim objFSO As FileSystemObject
                Dim AllFiles As Object
                Dim objFolder As Object
                Dim objFile As Object
                Dim FileExt As String
                Dim strSQL As String
                
                CreateInstrumentInterfaceLogRecords = False
                strSQL = "SELECT FileExt FROM tlkpInstrument"
                FileExt = ExecuteScalar(strSQL)
                
                
                Set objFSO = CreateObject("Scripting.FileSystemObject")
                Set objFolder = objFSO.GetFolder(NewPath) 
                
                'NewPath is a public variable that holds the path to the folder'
                
                Set AllFiles = objFolder.Files
                For Each objFile In AllFiles
                
                FileName = objFile.FileName
                QuenueId = Replace(FileName, FileExt, "")
                
                'call procedure to pass values'
                
                Next objFile
                
                ExitProc:
                Exit Function
                HandleError:
                MsgBox Err.Number & " " & Err.Description & " in CreateInstrumentInterfaceLogRecords"
                GoTo ExitProc
                
                End Function
                

                和存储过程是:

                CREATE PROCEDURE upInsertToInstrumentInterfaceLog @BatchID nvarchar(60),@InstrumentName nvarchar(60) ,@FIleName nvarchar(60), @QueueId nvarchar(60) 
                AS
                INSERT INTO tblInstrumentInterfaceLog (BatchID,InstrumentName,FileName,QueueID,DateOfBatch,Folder)
                VALUES (@BatchID, @InstrumentName,@FileName, @QueueId,getdate(),'New');
                GO
                

                所有的例子都没有给我一个创建连接和调用过程的可靠想法.我得到的一些建议是研究 ExecuteNonquery 的工作原理,所以我一直在努力寻找与此相关的示例.以下是我找到的示例模板之一

                All the examples haven't really given me a solid idea of to create the connection and call the procedure. Some advice I have been given is to study how ExecuteNonquery works so I have been trying to find examples related to that. The following is one of the example templates I've found

                Dim conn As ADODB.Connection 
                Dim cmd As ADODB.Command 
                
                Set conn = New ADODB.Connection 
                conn.ConnectionString = "your connection String here" 
                conn.Open 
                
                Set cmd = New ADODB.Command 
                cmd.ActiveConnection = conn 
                cmd.CommandType = adCmdStoredProc 
                cmd.CommandText = "put stored procedure name here" 
                
                cmd.Execute 
                conn.Close 
                
                Set conn = Nothing 
                Set cmd = Nothing 
                

                我不太确定我应该从这个例子中得到什么以及如何合并传递值.此外,即使我访问了 http://www.connectionstrings.com/ 我仍然对如何制作感到困惑他们.任何帮助将不胜感激

                I am not really sure what I should take from this example and how to incorporate passing values. Also even though I have visited http://www.connectionstrings.com/ I am still confused on how to make them. Any help would be greatly appreciated

                推荐答案

                Dim conn As ADODB.Connection 
                Dim cmd As ADODB.Command 
                
                Set conn = New ADODB.Connection 
                conn.ConnectionString = "your connection String here" 
                conn.Open 
                
                Set cmd = New ADODB.Command 
                cmd.ActiveConnection = conn 
                cmd.CommandType = adCmdStoredProc 
                cmd.CommandText = "upInsertToInstrumentInterfaceLog" 
                
                cmd.parameters.Append cmd.CreateParameter("@BatchID", adVarChar, adParamInput, 60, "value for BatchID")   
                cmd.parameters.Append cmd.CreateParameter("@InstrumentName", adVarChar, adParamInput, 60, "value for InstrumentName")   
                '...
                
                cmd.Execute 
                conn.Close 
                

                这篇关于在VBA中从访问模块传递参数时调用存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                Building a comma separated list?(建立一个逗号分隔的列表?)
                Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column(尽管每列都使用了 varchar(MAX),但在导入 CSV 文件时 SQL Server 中出现错误)
                How can I import an Excel file into SQL Server?(如何将 Excel 文件导入 SQL Server?)
                Export table to file with column headers (column names) using the bcp utility and SQL Server 2008(使用 bcp 实用程序和 SQL Server 2008 将表导出到带有列标题(列名称)的文件)
                Concat field value to string in SQL Server(将字段值连接到 SQL Server 中的字符串)
                SQL Server Bulk insert of CSV file with inconsistent quotes(SQL Server 批量插入带有不一致引号的 CSV 文件)
                <legend id='0SQxn'><style id='0SQxn'><dir id='0SQxn'><q id='0SQxn'></q></dir></style></legend>
              • <i id='0SQxn'><tr id='0SQxn'><dt id='0SQxn'><q id='0SQxn'><span id='0SQxn'><b id='0SQxn'><form id='0SQxn'><ins id='0SQxn'></ins><ul id='0SQxn'></ul><sub id='0SQxn'></sub></form><legend id='0SQxn'></legend><bdo id='0SQxn'><pre id='0SQxn'><center id='0SQxn'></center></pre></bdo></b><th id='0SQxn'></th></span></q></dt></tr></i><div id='0SQxn'><tfoot id='0SQxn'></tfoot><dl id='0SQxn'><fieldset id='0SQxn'></fieldset></dl></div>
                    <bdo id='0SQxn'></bdo><ul id='0SQxn'></ul>

                    <tfoot id='0SQxn'></tfoot>
                      <tbody id='0SQxn'></tbody>

                        <small id='0SQxn'></small><noframes id='0SQxn'>