Append Memo Fields from MS Access to SQL Server

[YisMan]

Hi everyone

How can I append records from an Access table to a SQL Server table?
regular append queries cut off the memo fields at 255 chars. no good.
i tried using adodb with sqloledb provider, bu then it doesnt find my access
table.
any ideas anyone?
i probaby can open recordsets manually and copy 1 row at a time, but this
will be complicated to maintain as fields are added, modified and removed.

 

[Rick Brandt]

Hi everyone

How can I append records from an Access table to a SQL Server table?
regular append queries cut off the memo fields at 255 chars. no good.
i tried using adodb with sqloledb provider, bu then it doesnt find my
access table.
any ideas anyone?
i probaby can open recordsets manually and copy 1 row at a time, but
this will be complicated to maintain as fields are added, modified
and removed.

There might be something wrong with your append query as it should not be
cutting off at 255 characters. Are you using Group By or applying a sort or
format property to the memo field in the query? If so that is what is doing the
truncating.

 

[Linq Adams via AccessMonster.com]

Here's a nice little paper by Allen Browne that explains the ins and outs of
truncation of memo fields by query and, if I remember correctly, some
workarounds.

http://allenbrowne.com/ser-63.html

 

[YisMan]

Hi Rick and thanks for taking interest.

First, Thanks for writing that it shouldn't be truncating. Now we just have
to find where the problem is.

I do not think that the problem is with the sql stqtement which is very
simply:

INSERT INTO dbo_Sections ( SectionText )
SELECT Sections.SectionText
FROM Sections;

As you see, there are no sorts or filters or anything like that. "Sections"
is a local access table in the mdb file and "dbo_Sections" is a SQL table
linked to the mdb file via ODBC.

I read somewhere in regard to some similar problem, that Access looks at the
first row and if its less then 255 chars, it regards it as text instead of
memo. so i also tried

INSERT INTO dbo_Sections ( SectionText )
SELECT Sections.SectionText
FROM Sections
ORDER BY Len([sectiontext]) DESC;

didn't work either. What can the matter be?

With Appreciation,
YisMan

 

[YisMan]

Thanks Linq

It's actually a great paper, as all of Allen Browne's papers are, and i've
actually came across it while i was googling on the subject.
Unfortunately, it does not mention this proplem at all, so i'm still stuck
here. Any other great ideas/links?

Thankfully,
YisMan

 

[Rick Brandt]

I do not think that the problem is with the sql stqtement which is very
simply:

INSERT INTO dbo_Sections ( SectionText )
SELECT Sections.SectionText
FROM Sections;

If you just look at the datasheet output of your SELECT statement, is the text
truncated?

I read somewhere in regard to some similar problem, that Access looks at the
first row and if its less then 255 chars, it regards it as text instead of
memo. so i also tried

That only applies to importing and then only when importing from a text file or
Excel where a real data type doesn't exist. When dealing with actual database
tables that does not come into play.

 

[YisMan]

Hi Rick.
Thanks for helping out.

In the datasheet view of the append query, the records are *not* truncated.

Thanks for the info about the first row.

Please keep posting.
Waiting for your valuable input,
YisMan

 

[Armen Stein]

Hi Rick.
Thanks for helping out.

In the datasheet view of the append query, the records are *not* truncated.

Thanks for the info about the first row.

Please keep posting.
Waiting for your valuable input,
YisMan

I seem to recall that older versions of the SQL ODBC drivers had
trouble with this. It's unlikely you are running one of these, but
you might check to make sure you have the latest MDAC service pack
installed.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[YisMan]

Hi Armen
First pleasecaccept my apologies. For some reason i was not notified of your
answer. Just now I was googling and I saw that you posted. So, sorry I'm late.

Now to our point.
How can I know if I have the latest MDAC? i have access 2003 with sp2. i
also have sql server 2005 express with vb and vwd 2005 express editions.
i downloaded from microsoft mDAC 2.8 but it refused to install with a
message saying that its part of windows.

If theres any connecton then in my mdb's references i have ado 2.8.

Meanwhile the problem still exists. To make sure thar it is not related to
the memo fields content. i made a vba function that returns a string of 300
regular ascii chars ( string(300,"t") ) . No go. only the first 255 get
across to the Sql Server table.

what, oh what can i do here?

Thank you all for chippng in!

Please keep the posts coming! i really need to get the info over to sql
server.

Thankfully,
YisMan

 

[Armen Stein]

How can I know if I have the latest MDAC? i have access 2003 with sp2. i
also have sql server 2005 express with vb and vwd 2005 express editions.
i downloaded from microsoft mDAC 2.8 but it refused to install with a
message saying that its part of windows.

Well, maybe MDAC isn't the problem. If you want to check version
numbers of the dll's, see the list here:
http://support.microsoft.com/kb/899456

Meanwhile the problem still exists. To make sure thar it is not related to
the memo fields content. i made a vba function that returns a string of 300
regular ascii chars ( string(300,"t") ) . No go. only the first 255 get
across to the Sql Server table.

To help narrow dow the issue, you could try exporting the fields into
CSV format. Try it directly on the table - don't use queries of
queries, as that can sometimes change field types.

Otherwise, I'm stumped - any other suggestions, anyone?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jamie Collins]

Otherwise, I'm stumped - any other suggestions, anyone?

What about a 'pull' rather than a 'push' e.g.

INSERT INTO dbo.Sections (SectionText)
SELECT SectionText
FROM OPENROWSET('MSDASQL','Driver={Microsoft Access Driver
(*.mdb)};DBQ=C:\MyPath\MyDB.mdb;SystemDB=C:\MyPath
\System.mdw;UID=Admin;PWD=mypassword','SELECT SectionText FROM
Sections')

Jamie.

--

 

[YisMan]

Hi Armen,
Thanks for getting back to me,

I checked the page you sent me, There is a complete list of dlls and version
numbers. It is quite beyond me to understand what im supposed to do with such
a long list. :-(

One sentence that is stated there is:
"This release provides the same version of MDAC that was released with
Microsoft Windows XP Service Pack 2 (SP2) and Microsoft SQL Server 2000
Service Pack 4 (SP4)."

Since i have xp sp2 and sql server express 2005 and automatic updates run on
my machine all the time, does that cover this possibility?

The second thought of yours was very good. using access' export to text
option i was able to create a full csv file for all records with complete
fields-no truncating. now, where does this lead us?

There probably is some tool for importing from csv to sql server. but really
it shouldnt be that complicated

thank you very much for your time and patience
it is really appreciated,
YisMan