CODEWITHSUNDEEP
excelvbams-accesssharepoint
Krukosz
Krukosz

Reputation: 31

ADODB Recordset cannot get whole multivalued field from Access

I have a problem with one project in my work. I have a database on Sharepoint. It's hooked into .accdb file (Access 2007/2010). So far, I used ADODB Connection with standard ConnectionString (only Provider - ACEDB 12.0).

When I try to get data from one of multivalued field from database the recordset is empty for this column. Example:

I have to get few columns: ID, Location, Name, People (MVF), Trainers (MVF).

When single record in People column has MORE than 3-4 values - the recordset for this column is empty. If there's less than 3-4 values i'm getting semicolon-separated values (Even a LEFT JOIN statement to get the source data of MVF doesn't make any difference)

I'm working on Excel - the End-user uses ONLY Excel.

When I watch a Recordset - it has empty values when the people's values should be placed - Basing on this I think the problem is caused by type of connection or something. I've tried also DAO connection - no positive results.

I've also tried to make a temporary database in .accdb file only to execute SQL (INSERT INTO tmpDB SELECT People FROM inputDB; -it's a pseudo-code, the syntax is good) And then I get "Cannot execute INSERT INTO for multivalued field".

I know, that the MVF is not recommended to use, but it's a SharePoint DB, and my role is only to get data from db to Excel.

Update

I tried to use the ODBC driver ...

objConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & myconn & ";Uid=Admin;Pwd=;"

... instead of the OLEDB provider ...

objConn.Provider = "Microsoft.ACE.OLEDB.12.0"
objConn.Open myconn

... but now the MVF are always empty.

Upvotes: 0

Views: 1245

Answers (1)

Krukosz
Krukosz

Reputation: 31

I resolved this problem. Here's what i've done. The code could have syntax errors. I post here code written from my memory - it's not copy of my working code.

The main and the most important thing is the type of connection. After reserch i found that Microsoft recommends using ADO connection. As I posted earlier, the DAO requires additional looping through recordset and it could be a problem and using DAO with Connection string doesn't look better than ADO.

The best and the only way to get data from MVFs is DAO, but the connection MUST be obtained by "OpenDatabase" method - in this case there's no problems with MVFs with big number of values.

Sub ImportMVFs()

 Dim dbs As DAO.Database
 Dim rsRecord As DAO.Recordset
 Dim rsChild As DAO.Recordset
 Dim strSQL As String
 Set dbs = "Path to database - works with .accdb too"
 Set db = ws.OpenDatabase(dbs) 'This type of connection is a best way to import from MVF.

 strSQL = "SELECT * FROM tblToImport;"
 Set rsRecord = db.OpenRecordset(strSQL)
 Debug.Print rsRecord.Field("Column1").Value
 Debug.Print rsRecord.Field("Column2").Value

 Do Until rsRecord.EOF

      Set rsChild = rsRecord.Field("MultiValuedFieldColumn") 

      Do Until rsChild.EOF
        Debug.Print rsChild.Field(0).Value 'We have to iterate through all mvfs
        'Here it's possible to make a temporary table in Access to reorganize MVFs into simple records
        'For example: Using SQLQuery as SQL string with Execute method.

        db.Execute SQLQuery
        rsChild.MoveNext
      Loop

      rsRecord.MoveNext

 Loop

 rsRecord.Close


 Set rsRecord = Nothing
 Set dbs = Nothing

End Sub

Upvotes: 0

Related Questions