Run StoredProc from Access to populate local holding table pt 2

[03Explorer]

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

 

[Andrzejek]

I think this is the name of the Procedure:

With cmd
  .ActiveConnection = cn
  .CommandText = [BLUE]"SEL_Suppliers_Summary"[/BLUE]
  .CommandType = adCmdStoredProc
  .Parameters.Refresh
End With

---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

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.

 

[Andrzejek]

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

With cmd
    .ActiveConnection = MyCon
    .CommandText = "[blue]The_Name_of_My_PROC[/blue]"
    .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.

 

[03Explorer]

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.

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

 

[Andrzejek]

just got my head spinning...

Which way...?

I have 3 parameters to send into the SPROC

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

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


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

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):

[blue]Exec STORED_PROCEDURE[/blue]

And then all the code you need is:

[blue]CurrentDb.Execute "SELECT * INTO YOUR_NEW_TABLE FROM ExecSP"[/blue]

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

 

[03Explorer]

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

[highlight #729FCF]Dim cn As New ADODB.Connection[/highlight]
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:

'-- 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

 

[03Explorer]

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].

 

[Andrzejek]

Is this related to needing a reference installed?

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

---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

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.

 

[strongm]

>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.

 

[03Explorer]

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

 

[03Explorer]

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

 

[Andrzejek]

I would start a new thread with this issue.


---- Andy

There is a great need for a sarcasm font.