Access TextBox with SQL VarChar(Max)

[RiskIt]

Hi,



I have a SQL back end database with access front end. In the back end I have a field which is set as VarChar(Max). Now, when entering the data into the access front end it still limits me to Long Text. I am looking for a solution to enable the access front end to show more than the limit for Long Text.

Any help would be great.

Thanks

[rpeare]

how much are we text are we talking about here. I've seen long text store up to about 4k characters so if you're typing more than than in a single field I'd be curious as to the why. at that point it may be better to have a link to an external document. The problem though is that you can't do a lot of things with long text (like search it, or maniuplate it) after the 255th character.

[RiskIt]

So the database is to store details for items that has been processed by a member of staff. Now the box I am focusing on here is one that stores serial numbers of the items processed. This box is for say when they process 2 or more of the same item all the serial numbers are stored here.
Each serial number can be anywhere from 4 to 15 characters now if you have anywhere from 2 to 90 serial numbers this adds up quickly.

But the text box will stop every time at the 255 mark but the table (SQL end) is set to varchar(max) which if I am correct should hold a lot more than 255 characters.

[Gicu]

Is the SQL table linked into the front-end? What driver are you using? If you open the table in design view in the front-end what is the data type shown for the field? Is the form's recordsource the SQL table itself or a query based on it (which could be the cause of your problem as in certain cases the queries are truncating the memo/long text fields)?

Cheers,
Vlad

[Micron]

This box is for say when they process 2 or more of the same item all the serial numbers are stored here.

That is a multi value field type of setup. Usually, every serial number ought to be its own record.

[RiskIt]

Yes, The table is linked from SQL to the front end. Its link by ODBC. The data type for the field is long text. The record source is the table itself.
Hope this helps

[RiskIt]

Is the SQL table linked into the front-end? What driver are you using? If you open the table in design view in the front-end what is the data type shown for the field? Is the form's recordsource the SQL table itself or a query based on it (which could be the cause of your problem as in certain cases the queries are truncating the memo/long text fields)?

Cheers,
Vlad

Yes, The table is linked from SQL to the front end. Its link by ODBC. The data type for the field is long text. The record source is the table itself.
Hope this helps

[RiskIt]

That is a multi value field type of setup. Usually, every serial number ought to be its own record.

So, This is to save the technician time so they can enter multiple items under one record but store all serial numbers for future reference should we need it.

[Micron]

It may save him a few seconds, not having to tab to a different row for additional serials, but it is probably going to cause you grief later. Or maybe it has already begun, given the current problem.

[RiskIt]

It may save him a few seconds, not having to tab to a different row for additional serials, but it is probably going to cause you grief later. Or maybe it has already begun, given the current problem.

They have 29 boxes of information to fill out for each item, So if they have multiple items so save them filling out these boxes for each record the could fill out the quantity box and then the box of multiple serial numbers (This is the box with the issue).

[Micron]

Not going to try to convince you otherwise. Time will tell if I am wrong or not. AFAIC, your problems have already begun. If you were dealing with a proper setup, you wouldn't be having this current issue. If that's not an indicator of your future path, there is nothing I can say that will convince you otherwise.
I wish you good luck with your db.

[Gicu]

Can you try to open the table directly (in the front end) and see what happens if you try to enter more than 255 characters in the field? What ODBC driver are you using? Sometimes this is caused by incompatibilities between the driver and the SQL server version ( I prefer sql server native client 11.0 over the legacy SQL Server driver that comes with Windows). Also I would try changing the datatype in the SQL server to Nvarchar(max).
https://social.msdn.microsoft.com/Fo...orum=accessdev

[RiskIt]

Can you try to open the table directly (in the front end) and see what happens if you try to enter more than 255 characters in the field? What ODBC driver are you using? Sometimes this is caused by incompatibilities between the driver and the SQL server version ( I prefer sql server native client 11.0 over the legacy SQL Server driver that comes with Windows). Also I would try changing the datatype in the SQL server to Nvarchar(max).
https://social.msdn.microsoft.com/Fo...orum=accessdev

I have tried entering data directly into the table and it does allow more than the 255 limit. But when I then look at the data in the form it only shows up to the 255 limit. The default driver was used that was already installed.

The Server/Host of the database is in work and do not have remote access to try altering the datatype at this time.

[Gicu]

Then the problem lies with the form, does the record source property show the entire table (tblYourSQLTable) or is it a SQL statement like SELECT * FROM tblYourSQLTable? If the later make sure there are no DISTINCT (see the link for possible causes of truncation) http://allenbrowne.com/ser-63.html

Cheers,
Vlad

[RiskIt]

The record source for the form shows just the table which is dbo_BARCODE.