Make table from SQL View, one char field now became MEMO

  • Thread starter Thread starter Gary Walter
  • Start date Start date
G

Gary Walter

Access 2000 Windows 2000 SQL Server 2000

On SQL Server, in one table have field ISBNHyph
( char, len=13, allow nulls) that contains ISBN nums
with hyphens.

In one View, I strip hyphens

REPLACE (ISBNHyph, '-', '') AS ISBNNoHyph

to get 10 chars of ISBN.

In Access front end, I have been running a make table
query that just makes that linked View into an Access table.

No problems for most of year.

When open one report, have been passing strWhere of

"[ISBNNoHyph] IN (Select [ISBN] From tblTransfers Where [SumTransfers]>0)"

Last week opening the report suddenly would choke with error:

Invalid Memo, OLE, or Hyperlink Object in
subquery '[ISBNNoHyph]'

******************
It turned out that make table query was *now* bringing
ISBNNoHyph over as a *MEMO*
******************

I don't know what we've done lately on
the SQL Server side as far as updates,
but I did apply Jet 4 SP8 recently.

The make table query is run through code

CurrentDb.Execute "qrymaktblAdoptions", dbFailOnError

and is just a "straight-across-the-board" every field
from linked View (listed individually, no "*") into a
new Access table.

I've worked around the problem now that I know
what was going on, so this is mostly just a "heads-up"
warning, unless someone knows what might have
changed?

Thanks,

Gary Walter
 
In general, it's hard to control what data types or sizes you get from a
make table query.

You might try instead to pre-create the destination table with the data
types and sizes you want, and then change the make table query into an
append query that inserts records into this table. Before running the
append query, you may want to run a delete query to delete existing records
from the destination table.
 
Brian Camire said:
In general, it's hard to control what data types or sizes you get from a
make table query.

You might try instead to pre-create the destination table with the data
types and sizes you want, and then change the make table query into an
append query that inserts records into this table. Before running the
append query, you may want to run a delete query to delete existing records
from the destination table.
Click to expand...
Amen Brian.

Of course you are right.
 
Back
Top