How Do You Search The Forum?
- Thread starter Joanchka
- Start date
When I have an Access question, I search via google. If needed you can use the advanced option of google and limit it to just this site.
Once you do find a thread on this forum that is close to what you are looking for, but not exactly, you can scroll to the bottom of the page and see if something better is in the 'Similar Threads' area.
Once you do find a thread on this forum that is close to what you are looking for, but not exactly, you can scroll to the bottom of the page and see if something better is in the 'Similar Threads' area.
- Local time
- Yesterday, 21:56
- Joined
- Feb 28, 2001
- Messages
- 29,995
Joanchka,
Part of the problem MIGHT be that you need to read some posts that are in the general area of concern to see the words we use for that topic. I have found that sometimes the problem is an obscure topic, but more often it is that you are using words not commonly used for that subject matter. In other words, the jargon of this forum is often important in learning how to ask the right question.
The other part of the problem is that if you have a problem with, say, "recordsets" then when you search for "recordsets" you get eleven or twelve pages of hits - if not more. But if your problem is really a "type mismatch" in that recordset, choose the more restrictive term.
Sadly, a LOT of the problems in this forum have titles that don't help. Many folks say "This is not working" as a title, and in the search results page, all you see is a title. That does not help very much, does it? But it is a reality in the forum. All I can tell you there is to persevere and eventually you should be able to find something relevant.
Speaking of which, was this post started because you have a current problem and have not yet found what you wanted? Follow up in this thread and maybe we can point to some good search words or phrases for you.
Part of the problem MIGHT be that you need to read some posts that are in the general area of concern to see the words we use for that topic. I have found that sometimes the problem is an obscure topic, but more often it is that you are using words not commonly used for that subject matter. In other words, the jargon of this forum is often important in learning how to ask the right question.
The other part of the problem is that if you have a problem with, say, "recordsets" then when you search for "recordsets" you get eleven or twelve pages of hits - if not more. But if your problem is really a "type mismatch" in that recordset, choose the more restrictive term.
Sadly, a LOT of the problems in this forum have titles that don't help. Many folks say "This is not working" as a title, and in the search results page, all you see is a title. That does not help very much, does it? But it is a reality in the forum. All I can tell you there is to persevere and eventually you should be able to find something relevant.
Speaking of which, was this post started because you have a current problem and have not yet found what you wanted? Follow up in this thread and maybe we can point to some good search words or phrases for you.
Thanks The Doc Man,
Yes, I have a problem which I don't know now if it is Access or Excel, but basically I want to link to an Excel spreadsheet and update one of my fields in access (it is a long text field)
If the characters in the Excel field exceed a certain amount (around 7k-8k including spaces) then the linking or update won't work.
It seems to be about the maximum characters that I can view in Excel, although I know the data is in the cell because I can copy the cell and paste it into notepad and everything is there.
Everything works okay if my Excel spreadsheet just has cells with a character count of under 7K, but if I introduce any greater than that then I get the error "External table is not in the expected format"
I tried searching this site with "External table is not in the expected format", both with and without quotes but got 500 results, and I looked through a few of the results but had no success.
I've now been on this for 9 hours
Yes, I have a problem which I don't know now if it is Access or Excel, but basically I want to link to an Excel spreadsheet and update one of my fields in access (it is a long text field)
If the characters in the Excel field exceed a certain amount (around 7k-8k including spaces) then the linking or update won't work.
It seems to be about the maximum characters that I can view in Excel, although I know the data is in the cell because I can copy the cell and paste it into notepad and everything is there.
Everything works okay if my Excel spreadsheet just has cells with a character count of under 7K, but if I introduce any greater than that then I get the error "External table is not in the expected format"
I tried searching this site with "External table is not in the expected format", both with and without quotes but got 500 results, and I looked through a few of the results but had no success.
I've now been on this for 9 hours
isladogs
MVP / VIP
- Local time
- Today, 03:56
- Joined
- Jan 14, 2017
- Messages
- 18,787
This may be a version specific issue.
Which versions of Excel and Access are you using -32-bit or 64-bit?
Am I correct that you have linked the Excel spreadsheet in Access?
If so, instead try importing it as a local table in Access.
Do you get the same behaviour when you try to update from a local table?
If it still fails, try making a csv (text) copy of your Excel file and repeat.
Which versions of Excel and Access are you using -32-bit or 64-bit?
Am I correct that you have linked the Excel spreadsheet in Access?
If so, instead try importing it as a local table in Access.
Do you get the same behaviour when you try to update from a local table?
If it still fails, try making a csv (text) copy of your Excel file and repeat.
Hi Ridders,
I'm using Access 2013 and Excel 2010, both 32 bit.
I have tried to import as a table, and get the same problem if the cell exceeds a certain size. When I try this the error is "The wizard is unable to access information in the file ((File location here) Please check that the file exists and is in the correct format"
I have also tried csv without success but I'll try and look at txt later
I'm using Access 2013 and Excel 2010, both 32 bit.
I have tried to import as a table, and get the same problem if the cell exceeds a certain size. When I try this the error is "The wizard is unable to access information in the file ((File location here) Please check that the file exists and is in the correct format"
I have also tried csv without success but I'll try and look at txt later
isladogs
MVP / VIP
- Local time
- Today, 03:56
- Joined
- Jan 14, 2017
- Messages
- 18,787
Txt and csv should give same result.
Can you upload the Excel file and stripped down copy of db?
isladogs
MVP / VIP
- Local time
- Today, 03:56
- Joined
- Jan 14, 2017
- Messages
- 18,787
Hi
Well I managed to import it both as a CSV file & tab delimited text file with the same results. Whether its any use to you is another matter.
I'm just going out for the evening so no time to look at it properly.
Hopefully someone else will step in
You might have more luck using the original HTML data
Good luck
Well I managed to import it both as a CSV file & tab delimited text file with the same results. Whether its any use to you is another matter.
I'm just going out for the evening so no time to look at it properly.
Hopefully someone else will step in
You might have more luck using the original HTML data
Good luck
- Local time
- Yesterday, 21:56
- Joined
- Feb 28, 2001
- Messages
- 29,995
After doing a little bit of digging, I found that Excel imposes a limit of 8192 characters in any formula. If somehow your content is viewed as a formula, that is where things get funky. And since your content is HTML that includes the "=" sign that introduces formulas, if it EVER gets confused, it might think there is a formula in there somewhere.
There is also the odd chance that this 8192 character limit for formulas ALSO applies to some of the methods used for cell mapping of spreadsheets to Access. And to the import methods suggested by Ridders.
Two thoughts:
1) Search this forum for "Import HTML" or "Import XML"
2) If you are feeling frisky, see about opening an Excel Application Object, then open the workbook, select the worksheet, and try to get the cell contents using VBA and an App object, which SHOULD bypass the import pre-processors.
There is also the odd chance that this 8192 character limit for formulas ALSO applies to some of the methods used for cell mapping of spreadsheets to Access. And to the import methods suggested by Ridders.
Two thoughts:
1) Search this forum for "Import HTML" or "Import XML"
2) If you are feeling frisky, see about opening an Excel Application Object, then open the workbook, select the worksheet, and try to get the cell contents using VBA and an App object, which SHOULD bypass the import pre-processors.
isladogs
MVP / VIP
- Local time
- Today, 03:56
- Joined
- Jan 14, 2017
- Messages
- 18,787
OK I had a few more minutes to spare so I also tried saving the Excel file as:
a) Unicode text
b) HTML
c) XML - fails as contains no XML tags
Imported both a) + b)
a) Unicode text still splits each line into separate records - same as CSV and TSV text files
b) HTML worked quite well - imported as one record - check if its all there as there was an Import error table
Finally I did the simplest method of all
Copied to the clipboard & pasted in Access - perfect! If a little impractical
I haven't got time to look into automation using TransferSpreadsheet or saved import specifications.
My advice would be to try and obtain the data not as an Excel file but as HTML or XML both of which can be imported easily into Access.
Alternatively if its available as a JSON file, I have an app to assist with importing those into Access
Updated file attached. Signing off now
a) Unicode text
b) HTML
c) XML - fails as contains no XML tags
Imported both a) + b)
a) Unicode text still splits each line into separate records - same as CSV and TSV text files
b) HTML worked quite well - imported as one record - check if its all there as there was an Import error table
Finally I did the simplest method of all
Copied to the clipboard & pasted in Access - perfect! If a little impractical
I haven't got time to look into automation using TransferSpreadsheet or saved import specifications.
My advice would be to try and obtain the data not as an Excel file but as HTML or XML both of which can be imported easily into Access.
Alternatively if its available as a JSON file, I have an app to assist with importing those into Access
Updated file attached. Signing off now
Last edited:
- Local time
- Today, 03:56
- Joined
- Jul 9, 2003
- Messages
- 17,334
There's a Blog here showing you how to use Google to specifically search Access World Forums (AWF) there's also a link to a web page setup ready to go!
Searching For Answers
Searching For Answers
- Local time
- Today, 03:56
- Joined
- Jul 9, 2003
- Messages
- 17,334
I'm sure they were all there when I stole it! I reckon some lurkers been up to mischief...
At the moment, I have a full set of everything, but I am showing signs of deterioration in certain places... I did happen to rip off a fingernail completely a few years back, it all grew back perfect! Mind you I did invent some new swear words at the time.
