0

Long story short, I am pulling a bunch of data from a data warehouse into a local Access DB. I'll post the code before showing what I'm doing with the first source data set. The rest of the data sets are being pulled in using the same concepts.

The local tables are pre-defined and the text fields in question are "memo" fields in the local DB. I checked and the source data being loaded into the data warehouse is not truncating these fields. Also I'm able to export the data via other applications without these fields being truncated. So there's something happening when I import the data from the data warehouse into my local access database.

Here's the relevant excerpt from my (very simple) code. I'm just matching field names and using the recordset .value property to copy the data from the server to the local table.

Any ideas?

Dim db As DAO.Database
Set db = CurrentDb

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Dim rsServer As ADODB.Recordset
Set rsServer = New ADODB.Recordset

Dim rsLocal As Recordset
Dim fField As ADODB.Field

'Open Connection
With conn
    .ConnectionString = dataConnectionString
    .Open
End With

'*****************************
'*******Data set 1 data*******
'*****************************

'Open server DSET_1_SOURCE_DATA table
Set rsServer = conn.Execute(SQL_DSET1_SERVER)

'Open local DSET_1_LOCAL_DATA table
Set rsLocal = db.OpenRecordset(SQL_DSET1_LOCAL)

'DELETE ALL RECORDS FROM LOCAL TABLE
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM DSET_1_LOCAL_DATA")
DoCmd.SetWarnings True

rsServer.MoveFirst

Do Until rsServer.EOF
    rsLocal.AddNew

    For Each fField In rsServer.Fields
        rsLocal.Fields(fField.Name).Value = rsServer.Fields(fField.Name).Value
    Next

    recordCount = recordCount + 1
    rsLocal.Update
    rsServer.MoveNext
Loop
RH224
  • 141
  • 1
  • 5
  • Is there a reason why you don't simply link the server table, and run `INSERT INTO DSET_1_LOCAL_DATA SELECT * FROM DSET_1_SOURCE_DATA` ? – Andre Sep 14 '18 at 10:32
  • If you need the recordset and copying field-by-field, you'll have to show us the ConnectionString to see what server and driver is used. – Andre Sep 14 '18 at 10:32
  • @Andre - I had some issues linking the tables, or copying the tables wholesale. I couldn't seem to execute opening a connection to a server and then linking or copying the entire source table to the local DB. Honestly, I was looking for a quick solution months ago and found this work and just went with it, even if it's not the most elegant solution. I'm open to doing this another way, but if since I have a deliverable due today I'm just trying to fix the truncation issue (at least) for now. – RH224 Sep 14 '18 at 12:10
  • It's an OLE connection. Specifically, the "provider" is SQLOLEDB.1. Here's the entire (partially redacted) string: "Provider=SQLOLEDB.1;Integrated Security=SSPI;PersServerist Security Info=False;Initial Catalog=[redacted];Data Source=[redacted];Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=[redacted];Use Encryption for Data=False;Tag with column collation when possible=False;" – RH224 Sep 14 '18 at 12:13
  • OK so I kind of solved my issue. Two issues - some of my queries were truncating the memo fields and then once I solved that, exporting to Excel truncates those memo fields. But the recordset field copying is working as intended. Can't believe I didn't confirm that first yesterday. – RH224 Sep 14 '18 at 13:24

2 Answers2

0

May be you should try this code for MEMO fields

rsLocal.Fields(fField.Name).AppendChunk(rsServer.Fields(fField.Name).GetChunk(rsServer.Fields(fField.Name).ActualSize))
4dmonster
  • 3,012
  • 1
  • 14
  • 24
  • Thank you - while this didn't solve my problem (since I actually didn't even have the problem I thought I had) I did some research on this and found it interesting. So if nothing else, you've helped teach me something even if it didn't relate to my solution. – RH224 Sep 14 '18 at 19:55
0

I solved my own issue. Which wasn't the issue I thought I had.

Data was copying from my server recordset into my local recordset just fine. It was also updating the appropriate tables just fine. What I didn't realize was that exporting from a SELECT query directly into EXCEL truncates memo fields after 255 characters. Making the final query into a table and then exporting that solved my problem.

Thank you to everyone who responded trying to help.

RH224
  • 141
  • 1
  • 5