Access 2007 locks up when exporting to SharePoint

[allin1joe]

I have a query that has a VBA function in the select statement (creates a comma delimited list from a child table). Whenever I attempt to export the results of that query to a SharePoint list, Access crashes. I get the "Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience. When I take this function out of the select statement, the export works. The function works fine if I just return the result of the query or even if I use the query in a report.

Any ideas?

Thanks

Joe

[zmbd]

That is a very vague description.
More than likely you're transfering something that sharepoint doesn't like so:

Please post the SQL of the query - remember to format it using the <CODE/> button.
Next we need the structure of the underlying tables that the query is built on... please use something along these:

Name: tbl_exampletabl ename
[examplfieldname _PK] autonumber primary-key
[examplefieldnam e_somename] text(50)-notindexed, required
[examplefieldnam e_yan] long
[FK_example] long; foriegn key 1:M with tbl_someotherta blename

Please also make note of any lookup-fields in the tables and post their underlying record source(s).

We may also need the code, if any, being used to transfer.

[TheSmileyCoder]

You seem to think its related to your function call. So start by checking whether that is the case. Change your current query to a make table query, making a local table in your database. Then export that table to SharePoint. If it exports fine, then chances are you are right and maybe the function call is part of the problem (I don't have SharePoint experience I should point out at this point).

If the query doesn't export then it might be that you have issues with incompatible datatypes, so you can look into that.


Step 1 of problem solving (And debugging) is usually to make sure you have narrowed down the problem to the root cause.

[allin1joe]

You seem to think its related to your function call. So start by checking whether that is the case. Change your current query to a make table query, making a local table in your database. Then export that table to SharePoint. If it exports fine, then chances are you are right and maybe the function call is part of the problem (I don't have SharePoint experience I should point out at this point).

If the query doesn't export then it might be that you have issues with incompatible datatypes, so you can look into that.


Step 1 of problem solving (And debugging) is usually to make sure you have narrowed down the problem to the root cause.

Yeah, I do have it narrowed down to the function call. I have removed the function call and replaced with a hard coded comma delimited string and it works.

To the other poster, here is my query:

SELECT Project.SPRF, 
     Project.PROJECT_NAME, 
     Multi_Rec_List(Project.Project_ID,"PROGRAM") AS Program_list, 
     Multi_Rec_List(Project.Project_ID,"INITIATIVE") AS Initiative_list, 
     Multi_Rec_List(Project.Project_ID,"PROGRAM_MGR") AS Program_Mgr_list, 
     Multi_Rec_List(Project.Project_ID,"PROJECT_MGR") AS Project_Mgr_list, 
     Multi_Rec_List(Project.Project_ID,"BA") AS BA_list,
     Multi_Rec_List(Project.Project_ID,"CONTENT_MGR") AS CM_list, 
     Multi_Rec_List(Project.Project_ID,"AGENCY") AS Agency_list, 
     Multi_Rec_List(Project.Project_ID,"ONLINE_MGR") AS Online_list, 
     Multi_Rec_List(Project.Project_ID,"BUSINESS_LEAD") AS Bus_Lead_list, 
     Project_Type.PROJECT_TYPE_NAME, 
     Project.UCMG_CD, Release.RELEASE_DATE
FROM (Project LEFT JOIN Project_Type ON Project.PROJECT_TYPE_ID = Project_Type.PROJECT_TYPE_ID) LEFT JOIN Release ON Project.RELEASE_ID = Release.RELEASE_ID;

Here's the function:

Public Function Multi_Rec_List(In_Project_ID As Integer, In_List_Type As String)
  Dim RecordSt As DAO.Recordset
  Dim stringSQL As String
  Dim dBase As DAO.Database
  Dim Rec_List As String
  
  Select Case In_List_Type
    Case "PROGRAM"
      stringSQL = "SELECT Program_Name As List_Desc From Program PG, Project_Program PP where PG.Program_ID = PP.Program_ID and PP.Project_ID = " & In_Project_ID & " ORDER BY PROGRAM_NAME;"
    Case "INITIATIVE"
      stringSQL = "SELECT Initiative_Name As List_Desc From Initiative PG, Project_Initiative PP where PG.Initiative_ID = PP.Initiative_ID and PP.Project_ID = " & In_Project_ID & " ORDER BY Initiative_NAME;"
    Case "PROGRAM_MGR"
      stringSQL = "SELECT LEFT(Resource_First_Name,1) & "". "" &Resource_Last_Name as List_Desc FROM Resource R, Project_Resource PR WHERE R.RESOURCE_ID = PR.RESOURCE_ID and PR.Project_ID = " & In_Project_ID & " and PR.RESOURCE_TYPE_ID = 6 ORDER BY Resource_Last_Name;"
    Case "BUSINESS_LEAD"
      stringSQL = "SELECT LEFT(Resource_First_Name,1) & "". "" &Resource_Last_Name as List_Desc FROM Resource R, Project_Resource PR WHERE R.RESOURCE_ID = PR.RESOURCE_ID and PR.Project_ID = " & In_Project_ID & " and PR.RESOURCE_TYPE_ID = 5 ORDER BY Resource_Last_Name;"
    Case "PROJECT_MGR"
      stringSQL = "SELECT LEFT(Resource_First_Name,1) & "". "" &Resource_Last_Name as List_Desc FROM Resource R, Project_Resource PR WHERE R.RESOURCE_ID = PR.RESOURCE_ID and PR.Project_ID = " & In_Project_ID & " and PR.RESOURCE_TYPE_ID = 1 ORDER BY Resource_Last_Name;"
    Case "BA"
      stringSQL = "SELECT LEFT(Resource_First_Name,1) & "". "" &Resource_Last_Name as List_Desc FROM Resource R, Project_Resource PR WHERE R.RESOURCE_ID = PR.RESOURCE_ID and PR.Project_ID = " & In_Project_ID & " and PR.RESOURCE_TYPE_ID = 2 ORDER BY Resource_Last_Name;"
    Case "CONTENT_MGR"
      stringSQL = "SELECT LEFT(Resource_First_Name,1) & "". "" &Resource_Last_Name as List_Desc FROM Resource R, Project_Resource PR WHERE R.RESOURCE_ID = PR.RESOURCE_ID and PR.Project_ID = " & In_Project_ID & " and PR.RESOURCE_TYPE_ID = 3 ORDER BY Resource_Last_Name;"
    Case "ONLINE_MGR"
      stringSQL = "SELECT LEFT(Resource_First_Name,1) & "". "" &Resource_Last_Name as List_Desc FROM Resource R, Project_Resource PR WHERE R.RESOURCE_ID = PR.RESOURCE_ID and PR.Project_ID = " & In_Project_ID & " and PR.RESOURCE_TYPE_ID = 7 ORDER BY Resource_Last_Name;"
    Case "AGENCY"
      stringSQL = "SELECT DEPARTMENT_NAME as List_Desc FROM Budget B, Department D WHERE B.DEPARTMENT_ID = D.DEPARTMENT_ID and B.Project_ID = " & In_Project_ID & " and D.DEPT_CAT_ID = 1 ORDER BY Department_Name;"
  End Select
  Set dBase = CurrentDb

  Set RecordSt = dBase.OpenRecordset(stringSQL)
  Rec_List = ""
  If Not (RecordSt.EOF And RecordSt.BOF) Then
    RecordSt.MoveFirst
    Do Until RecordSt.EOF = True
      If Rec_List = "" Then
        Rec_List = RecordSt!List_Desc
      Else
        Rec_List = Rec_List & ", " & RecordSt!List_Desc
      End If
      RecordSt.MoveNext
    Loop
  End If
  RecordSt.Close
  dBase.Close
  Multi_Rec_List = Rec_List
End Function

As for the tables:

Project:
PK -- PROJECT_ID Autonumber
UCMG_CD -- Text
SPRF -- Text
Project Name -- Text
Project_Type_ID -- Number
Release_ID -- Number

Project_Type:
PK -- Project_Type_ID AutoNumber
Project_Type -- Text

Release:
PK -- Release_ID Autonumber
Release_Date -- Date/Time

I think that covers all tables/columns that are in the query.

I tried removing all of them and replacing with hard coded strings and it worked. I put 1 of them back and it crashes.

Thanks

Joe

[TheSmileyCoder]

Sorry, then. I don't really have any experience in sharepoint so I have nothing further to add here.

[zmbd]

put a STOP in your function just after the DIM. Once the code breaks to debug, [F8] thru the code. As I do not have your database it's hard to tell which line the engine is choking on...

[allin1joe]

I put the stop in the function right after the last DIM statement. I right clicked on the query and chose Export to SharePoint list. I didn't get any debug messages. It went through a couple of different windows, and crashed when it got to "Copying Data to SharePoint List for Pipeline_Test (33%)".

[zmbd]

Your function is returning a list of values into a select statment? So you're getting something like:

SELECT Project.SPRF,  
     Project.PROJECT_NAME,  
     Multi_Rec_List(Project.Project_ID,"PROGRAM") AS Program_list, 
(...)

==

SELECT Project.SPRF,  
     Project.PROJECT_NAME,  
     "value1, value2, value3" AS Program_list, 
(...)

Post an example of the rendered SQL.

[allin1joe]

You are correct. The data looks something like this:

SPRF/Project Name/Program List
1234/Project 1/Program 1, Program 2
2345/Project 2/Program 1, Program 3, Program 4

Basically, I'm looping through the child tables and returning all of the values for this project in a comma delimited list so I can show them in one field.

I can't post a screen shot of my actual data as it's sensitive information.

Thanks

Joe

[zmbd]

That's what I thought...

- I am also guessing that you're passing that SQL with the functions as is to the code making the transfer? (which we've yet to see, would you post the code where the actual transfer is taking place)

- The issue may be that the SQL engine can not execute the VBA function.

- What you should do, is build the SQL string and then pass the final string.

- Also, I've never used a comma delimited list in an SQL as a field name... I don't that is possible in Access; however, I'll let someone with a bit more experience speak on that.

[allin1joe]

I posted the SQL that calls the function in post #4. Basically, it's a call to the VBA function in the select statement. The query works fine whether I run the query independent or as the source of a report. The issue I run into when trying to export the result to a SharePoint list.

Returning a comma delimited list using pure SQL isn't possible in Access. That's why I'm using the VBA function. If you have other ideas to return what I'm looking for, I'm definitely open to anything at this point :).

Thanks

Joe

[zmbd]

What I'm asking for is the VBA that you are passing the SQL to for exection... or am I to understand that you have opened a query in SQL view and entered the posted SQL as a the query definition?

As far as I know, one can not use a comma deliminated list in a SQL for any query in Access in the the manner that you are using... and I suspect the programmers never expected anyone to attempt what you're doing, the program simply hangs as it doesn't have an error trap. I've been looking thru my sharepoint texts and I've not seen anything like what you're doing (yet! - only so much time between family (1st priority) and my hobby :) ).

If you've borrowed this method from somewhere would you either please post the link or provide citation to the information.

[allin1joe]

I'm new to Access and VBA, but have been doing database programming for 20 years. Everything I've done is "borrowed from somewhere" :), but personalized to do what I needed to do. I wrote the query at first to be the source of a report. The need then arose to use it in SharePoint, so I tried the built in SharePoint export functionality which hangs. That's when I copy/pasted the query into an actual query, which again works in Access, but crashes when trying to export to SharePoint.

The basic logic is the VBA function that I posted in post #4, 2nd code block. That is in the main package of Access (sorry, I forgot the name and don't have Access open right now). I called that function from the select statement, which works just fine in Access. It just doesn't want to export to SharePoint. I have no other code written other than what I posted in #4.

I looked into a field that could hold multiple values, but I saw a ton of information saying that it shouldn't be used. That's when I went to this function approach. My main goal is to see all of the child information associated with the project, but be able to show it on one record without duplication. If there is any other way that I can do that without the VBA function, I'm definitely open to suggestions.

Thanks

Joe

[zmbd]

The mulitple value field and lookup fields have one exception for use...

Sharepoint

There are things that MS decided to force the use these non-standard types of fields for use between Sharpoint and Access.

As for databases... I've been working with one for or another for 20 say almost 30 years as well, most of it self taught and starting as a teenager back in the 80s - I've cut my teeth with the Access stuff since version 1 and glad we skipped V2007.

In this case, from what I gather from what little you've posted: you are going to have to do a make table query with this... From what I can find so far, you can not send the function to the SQL engine for transfer.

[allin1joe]

"In this case, from what I gather from what little you've posted: "

I don't know what else to post. I posted all of my code and the procedures that I'm using to try to export that data to SharePoint.

Are you suggesting that I create a temporary table through VBA with the data and try to send it to SharePoint that way? I'll look into the make table function tonight.

Thanks!

Joe