PASS PARAMTERS FROM MS ACCESS 2003 to SQL SERVER

T

Tallboy

Registered User.

Local time

Today, 12:10

Joined

Oct 13, 2011

Messages

19

• Oct 13, 2011

• #1

Hi Guys,

I am moving to SQL Server and will be using MS Acess 2003 specifically as a Front End to a SQL Server 2008 database.

I have a form called FrmStats and a txt field called SName.

When I click on a button I want to send the txt as a parameter to a Stored Procedure on SLQ Server.

I know the code will look something like this

[FONT=&quot]Dim DB As Database
Dim Q As QueryDef

Set DB = CurrentDb()
Set Q = DB.QueryDefs(" ???? ")

Q.SQL = "exec dbo.ReturnRecord " +"'Forms!FrmStats.SName'"


Also how then do I show the results on another form?

Regards


[/FONT]

 

M

mdlueck

Sr. Application Developer

Local time

Today, 07:10

Joined

Jun 23, 2011

Messages

2,636

• Oct 13, 2011

• #2

I am executing SQL Server Stored Procedures via ADO objects. Here is some example code from the Insert method of a table class:

Code:

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjProductsTbl_Insert"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@authid").Value = Me.authid
    .Parameters("@projectid").Value = Me.projectid
    .Parameters("@title").Value = Me.title
    .Parameters("@productnumber").Value = Me.productnumber
    .Parameters("@bomcad").Type = adLongVarChar
    .Parameters("@bomcad").Value = Me.bomcad
    Set adoRS = .Execute()
  End With

  'Retrieve the ID the new record is stored at
  Me.id = Nz(adoRS!id, 0)

Usually executing .Parameters.Refresh has the Access FE know all about the SP's Parameters. The one gottcha with this is for a VARCHAR(MAX) which Access auto detects that Parameter as a VARCHAR(8000). This I had to manually update that Parameter's data type.

 

Last edited: Oct 13, 2011

T

Tallboy

Registered User.

Local time

Today, 12:10

Joined

Oct 13, 2011

Messages

19

• Oct 13, 2011

• #3

Hi,

Thanks for that, I think I get it but need to put the practice in.

I will no doubt be comming back to you as my project progressess.

kind regards

 

M

mdlueck

Sr. Application Developer

Local time

Today, 07:10

Joined

Jun 23, 2011

Messages

2,636

• Oct 13, 2011

• #4

You are welcome.

FYI: I am having quite a fight trying to blank out a date column type field via the Stored Procedure. So, all is NOT simple it seems when dealing with Date data types. Works fine at the Management Studio interface with bare SQL, fighting tooth-n-nail via Accesss / VBA / ADO / Stored Proc.

 

M

mdlueck

Sr. Application Developer

Local time

Today, 07:10

Joined

Jun 23, 2011

Messages

2,636

• Oct 14, 2011

• #5

FYI: I have resolved the trouble storing dates to SQL Server via ADO objects and Stored Procedures:

http://www.access-programmers.co.uk/forums/showthread.php?p=1104450#post1104450

 

Fear Naught

Kevin

Local time

Today, 12:10

Joined

Mar 2, 2006

Messages

229

• Feb 14, 2012

• #6

I have tried using this code but get an error when compiling the VBA on the ADODB.Command line.

I have added a "reference" to "Microsoft ADO Ext 6.0 for DDL and Security" (the only ADO Reference I can find). What have I done wrong!

Actually what I am trying to do is run a SQL Stored procedure that doesn't have any parameters but does do a large update across the database. I can of course run the procedure from SQL Management Studio but need to make it available from an Access fonrt end.

Help

 

M

mdlueck

Sr. Application Developer

Local time

Today, 07:10

Joined

Jun 23, 2011

Messages

2,636

• Feb 14, 2012

• #7

Fear Naught said:

I have added a "reference" to "Microsoft ADO Ext 6.0 for DDL and Security" (the only ADO Reference I can find). What have I done wrong!

Click to expand...

I believe the standard ADO objects are provided through "Microsoft ActiveX Data Objects 2.8 Library" (or what ever level of ADO your systems have installed.

The other ADO related reference I have enabled is "Microsoft ADO Ext. 2.8 for DDL and Security" and I believe that was required for some ADOX code not specifically related to executing Stored Procedures.

 

Fear Naught

Kevin

Local time

Today, 12:10

Joined

Mar 2, 2006

Messages

229

• Feb 14, 2012

• #8

Many thanks. That has worked.

Another good link is:

http://www.sqlservercentral.com/Forums/Topic657473-131-1.aspx where an example database is available.

 

M

mdlueck

Sr. Application Developer

Local time

Today, 07:10

Joined

Jun 23, 2011

Messages

2,636

• Feb 14, 2012

• #9

Fear Naught said:

That has worked.

Click to expand...

I am glad to hear your problem is now resolved.

 

A

ArtfulOne

New member

Local time

Today, 07:10

Joined

Sep 12, 2011

Messages

1

• Mar 8, 2013

• #10

What I am very unclear on is, "Where do the results go when I invoke a stored procedure from Access?

Suppose I want to use the returned result set as the data source for an Access form or report. Actually, I want to do both. I also want to know how to use a result set returned from a stored procedure or a view as the data source form a combo box or listbox.

Almost forget. If I do bind the result from a stored procedure to a form, is the data read-only? Ideally, I would like it to be read-write, and even be able to insert and delete. For example:

user is looking for a customer in Utah. There is a stored procedure that accepts a State parameter. User chooses Utah from a combo box. I pass that to the Stored Procedure. Form fills with a list of Utah customers. User realizes that the customer doesn't exist. User adds the customer to the list. Alternatively, user realizes that the address returned for an existing customer is old and needs updating.

Thanks in advance for any help you can provide.

Arthu

 

M

mdlueck

Sr. Application Developer

Local time

Today, 07:10

Joined

Jun 23, 2011

Messages

2,636

• Mar 8, 2013

• #11

ArtfulOne said:

What I am very unclear on is, "Where do the results go when I invoke a stored procedure from Access?

Click to expand...

It ends up in the same place a Pass-Through result set would end up. If using ADO.Command / ADO.Parameters objects, then the call to execute returns an ADO.RecordSet.

If in DAO.QueryDef objects, then it is possible to download records to an FE table.

Example posts:

Options to execute Stored Procedures - DAO / ADO - Including an example of SQL SELECT Stored Procedure using ADODB.Command and ADODB.Parameters objects
http://www.access-programmers.co.uk/forums/showthread.php?p=1160494&posted=1#post1160494

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605