×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Run StoredProc from Access to populate local holding table pt 2

Run StoredProc from Access to populate local holding table pt 2

Run StoredProc from Access to populate local holding table pt 2

(OP)
I found code that describes what I want to do... Execute a SPROC (MS SQL Server) and directly populates a local MS Access Table.

I am just uncertain in their code what the actual SPROC name is. I am not a member of that forum and rely on this site for my primary resource.

Thanks,
Site: Link

RE: Run StoredProc from Access to populate local holding table pt 2

I think this is the name of the Procedure:

CODE

With cmd
  .ActiveConnection = cn
  .CommandText = "SEL_Suppliers_Summary"
  .CommandType = adCmdStoredProc
  .Parameters.Refresh
End With 


---- Andy

There is a great need for a sarcasm font.

RE: Run StoredProc from Access to populate local holding table pt 2

(OP)
But that "SEL_Suppliers_Summary" is only used in the question, but not again in the resolutions. =\

Why not in the resolution suggestions below the question.

RE: Run StoredProc from Access to populate local holding table pt 2

This is what I have (in my VB6 code) where I call a Stored Procedure (in Oracle data base) that accepts 3 parameters:

CODE

With cmd
    .ActiveConnection = MyCon
    .CommandText = "The_Name_of_My_PROC"
    .CommandType = adCmdStoredProc

    .Parameters.Append .CreateParameter(, adInteger, adParamInputOutput, 5)
    .Parameters.Append .CreateParameter(, adVarChar, adParamInputOutput, 400)
    .Parameters.Append .CreateParameter(, adInteger, adParamInputOutput, 5)
    
    cmd(0) = 0
    cmd(1) = gUserName
    cmd(2) = cboPPC.ItemData(cboPPC.ListIndex)

    .Execute
End With
Set cmd = Nothing 


---- Andy

There is a great need for a sarcasm font.

RE: Run StoredProc from Access to populate local holding table pt 2

(OP)
Andy, you just got my head spinning... LOL

I took the question and solutions and put them into code I recognize (now you reminded me I have 3 parameters to send into the SPROC). I need to adjust this sample code to incorporate what you just shared (parameters to SPROC). My platform is MS SQL Server where SPROC exists, not sure if that matters.

CODE --> code

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim SQL As String

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

cn.connectionString = "provider=msdatashape; data provider=SQLOLEDB; Server=123.456.789.012; DATABASE= My_DB; UID=ABCD; PWD=1234;"
cn.Open

With cmd
  .ActiveConnection = cn
  .CommandText = "SEL_Suppliers_Summary"  ''SPROC Name
  .CommandType = adCmdStoredProc
  .Parameters.Refresh
End With

With rs
  .ActiveConnection = cn
  .CursorType = adOpenForwardOnly
  .CursorLocation = adUseServer
End With

Set rs = cmd.Execute
Set Me.Recordset = rs

While NOT rs.EOF
    SQL = "INSERT INTO T_Suppliers( "
    SQL = SQL + "Supplier_No, "
    SQL = SQL + "Supplier_Code, "
    SQL = SQL + "Supplier, "
    SQL = SQL + "Address_1, "
    SQL = SQL + "Address_2, "
    SQL = SQL + "Address_3, "
    SQL = SQL + "Address_4, "
    SQL = SQL + "Address_5, "
    SQL = SQL + "Phone_No, "
    SQL = SQL + "Fax_No, "
    SQL = SQL + "Credit_Limit, "
    SQL = SQL + "Account_Status_No, "
    SQL = SQL + "Active_Record, "
    SQL = SQL + "Entered_By, "
    SQL = SQL + "Entered_Date, "
    SQL = SQL + "Entered_IP_Address, "
    SQL = SQL + "Modified_By, "
    SQL = SQL + "Modified_Date, "
    SQL = SQL + "Modified_IP_Address) "
    SQL = SQL + "VALUES('" & rs("Supplier_No") & "', '" &
    SQL = SQL + rs("Supplier_Code") &  "', '" &
    SQL = SQL + rs("Supplier") &  "', '" &
    SQL = SQL + rs("Address_1") &  "', '" &
    SQL = SQL + rs("Address_2") &  "', '" &
    SQL = SQL + rs("Address_3") &  "', '" &
    SQL = SQL + rs("Address_4") &  "', '" &
    SQL = SQL + rs("Address_5") &  "', '" &
    SQL = SQL + rs("Phone_No") &  "', '" &
    SQL = SQL + rs("Fax_No") &  "', '" &
    SQL = SQL + rs("Credit_Limit") &  "', '" &
    SQL = SQL + rs("Account_Status_No") &  "', '" &
    SQL = SQL + rs("Active_Record") &  "', '" &
    SQL = SQL + rs("Entered_By") &  "', '" &
    SQL = SQL + rs("Entered_Date") &  "', '" &
    SQL = SQL + rs("Entered_IP_Address") &  "', '" &
    SQL = SQL + rs("Modified_By") &  "', '" &
    SQL = SQL + rs("Modified_Date") &  "', '" &
    SQL = SQL + rs("Modified_IP_Address") & "') "

  CurrentDb.Execute SQL, dbFailOnError
  rs.MoveNext

Loop

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

errbox:
  If Err.Number > 0 Then
    MsgBox Err.Description & " " & Err.Number
    Exit Sub
  End If 

RE: Run StoredProc from Access to populate local holding table pt 2

Quote (03Explorer)

just got my head spinning...

Which way...? spin spin2 spineyes

Quote (03Explorer)

I have 3 parameters to send into the SPROC

I knew that. That's why I've mentioned it smile (I should go to Las Vegas...)

But - did you get your code to work...?


---- Andy

There is a great need for a sarcasm font.

RE: Run StoredProc from Access to populate local holding table pt 2

Ok, so, by reverting to DAO basically we can do this with a lot less code ...

Create a Passthrough Query (for purposes of example call it ExecSP) in Access (you'll have to work out the connection string for yourself):

CODE --> Stored_Procedure

Exec STORED_PROCEDURE 

And then all the code you need is:

CODE

CurrentDb.Execute "SELECT * INTO YOUR_NEW_TABLE FROM ExecSP" 

obviously in a production environment you'll need to include some checks for if the table already exists, etc - but this should get you started

RE: Run StoredProc from Access to populate local holding table pt 2

(OP)
I am a lightly seasoned VBA programmer but this is a time where I ponder on why I get a compiling error with this command with Error: "User-defined type not defined" line in blue

CODE -->

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim SQL As String

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset 

Is this related to needing a reference installed?

My full code:

CODE -->

'-- SPROC execution section

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

cn.ConnectionString = "ODBC;DSN=SCA_Perform;Description=HR Proficiency;Trusted_Connection=Yes;APP=Microsoft Office 2016;DATABASE=SCA_Perform"
''    "provider=msdatashape; data provider=SQLOLEDB; Server=123.456.789.012; DATABASE= My_DB; UID=ABCD; PWD=1234;"
cn.Open

With cmd
  .ActiveConnection = cn
  .CommandText = "SP_WhoCanIsee2"  ''SPROC Name
  .CommandType = adCmdStoredProc

  .Parameters.Append .CreateParameter(, adInteger, adParamInputOutput, 1)
  .Parameters.Append .CreateParameter(, adVarChar, adParamInputOutput, 9)
  .Parameters.Append .CreateParameter(, adInteger, adParamInputOutput, 1)
  
  cmd(0) = 1                ''-- 1=Run MS Access Code; 2=Run MS SQL code
  cmd(1) = strEmployeeID    ''-- Root AssociateID
  cmd(2) = SqlToRun         ''-- What SQL is needed to be executed
  
  .Execute
End With

With rs
  .ActiveConnection = cn
  .CursorType = adOpenForwardOnly
  .CursorLocation = adUseServer
End With

Set rs = cmd.Execute
Set Me.Recordset = rs

While Not rs.EOF
    SQL = "INSERT INTO tblSecurityWhoCanIsee2( "
    SQL = SQL + "AssociateID, "
    SQL = SQL + "ManagerID, "
    SQL = SQL + "ManagerPrefName, "
    SQL = SQL + "AssociatePrefName, "
    SQL = SQL + "Title, "
    SQL = SQL + "EmployeeLevel) "
    
    SQL = SQL + "VALUES('" & rs("AssociateID") & "', '"
    SQL = SQL + rs("ManagerID") & "', '"
    SQL = SQL + rs("ManagerPrefName") & "', '"
    SQL = SQL + rs("AssociatePrefName") & "', '"
    SQL = SQL + rs("Title") & "', '"
    SQL = SQL + rs("EmployeeLevel") & "') "

  CurrentDb.Execute SQL, dbFailOnError
  rs.MoveNext

Loop

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

errbox:
  If Err.Number > 0 Then
    MsgBox Err.Description & " " & Err.Number
    Exit Sub
  End If 

RE: Run StoredProc from Access to populate local holding table pt 2

(OP)
Strongm - I am willing to work with code to avoid having Passthrough queries hanging out. My previous coding associate was all about having queries and passthrough queries (which you helped us GREATLY to understand). I like to keep the queries section as clutter free as possible and keep the power in the code. I've inherited code from too many programmers who had queries all over the place and it was too much work to decipher their madness. By this I look for coding solutions rather than Access queries.

I agree they are clean when in [design mode].

RE: Run StoredProc from Access to populate local holding table pt 2

Quote (03Explorer)

Is this related to needing a reference installed?

Yes. To use ADODB you need a Reference to Microsoft ActiveX Data Objects X.X Library


---- Andy

There is a great need for a sarcasm font.

RE: Run StoredProc from Access to populate local holding table pt 2

(OP)
How can I use ADO with ODBC? From what I am reading I have to use OLE DB. That forces me to use static server values vs using ODBC which our IT manages and we use when moving SQL servers.

RE: Run StoredProc from Access to populate local holding table pt 2

>I like to keep the queries section as clutter free as possible

It isn't a real SQL query though - it is just a proxie for running the SQL Server stored procedure

But have it your way with reams of code versus a single line of code and a tiny, clear, uncomplicated passthrough query. I know which one I think is more maintainable, and faster.

RE: Run StoredProc from Access to populate local holding table pt 2

(OP)
Okay Strongm, You won this time... :)

I did end up using a Passthrough query. I am not a fan of having a bunch of queries sitting in a list and no way 'easy' to quantify if they are used or not. That is the reason I chose to go with coding vs a TON of randomly plotted queries. Some need to be sunset but figuring out which is a task upon itself a thing I don't have time.

Until I can figure out how to use that code with ODBC, I am using the passthrough. Not happy about it, but it does get a job done... for now.

Rob

RE: Run StoredProc from Access to populate local holding table pt 2

(OP)
What is the best way to find out if queries or tables are used throughout the Access system? Either by query to query or VBA SQL code or by Objects? Is there any good solution to cleaning house on them without the change the name and let time reveal if it is used by way of broken code. This is and has been my issue with having so many queries in the system... if I just use VBA SQL it is isolated and easily resolved by searching all VB code.

Rob

RE: Run StoredProc from Access to populate local holding table pt 2

I would start a new thread with this issue.


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close