Calling Stored Procedure with parameters from Access Module in VBA

First for some background on my situation:

I am working in Access 2010 user frontend 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 the name of 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. Calling the stored procedure requires a connection string.

I have been researching connection strings and I am very lost. I am not really sure of how to obtain the information the connection string requires. I get an error invalid authorization specification. My current code for calling the stored procedure is:

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb;Database=GSATEST ;"
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, BatchID)
cmd.Parameters.Append cmd.CreateParameter("@InstrumentName", adVarChar, adParamInput, 60, InstrumentName)
cmd.Parameters.Append cmd.CreateParameter("@FileName", adVarChar, adParamInput, 60, FileName)
cmd.Parameters.Append cmd.CreateParameter("@QueueId", adVarChar, adParamInput, 60, QuenueId)

cmd.Execute
conn.Close

What format should I be using for my connection string and does anyone know an easier way to call a stored procedure perhaps without a connection string?

0 answers