ISTech Support Forum - Data Access in MS Access 2002

Browser

I love YaBB 1G -
SP1!

Posts: 8

Data Access in MS Access 2002
10/27/03 at 15:00:43

Hi All,

I have been using MS Access 2002 to attach (link) to DBA tables for the past 6 weeks with great success via ODBC.

This morning, I tried to access a table I have already linked to, MTICMSTR and I receive this error message;

“MTIC_PROD_SPECS[ 1] is not a valid name. Make sure that it doesn’t include invalid characters or punctuation and that it is not too long.”

I have rebooted the server, CHKDSK'd it and run DISKEEPER (a defrag ustility).

I have also re-indexed that particular file in DBA to no avail.

The really wierd thing is I have used this exact configuration for 6 weeks without a problem.
I can still access all the other files in DBA without a problem.

Has anyone seen this eror B4 and can you help me fix it?

Thanks!

James Taylor
http://www.AICompany.com
Web Design, Web Hosting and Search Engine Optimization

Back to top

IP Logged

Tommy Johnson

Browser

Posts: 36
Gender: male

Re: Data Access in MS Access 2002
Reply #1 - 10/27/03 at 16:04:10

DBA is not very good at not allowing invalid data input sometimes. That in combination with constant betrieve issues can cause problems. I have not ran into that problem with Access. You might try dropping the table link and reattaching, but more than likey you have some bad data in that table.

You might also try to use the DBA Maintain Database or export MTICMSTR to a text file, then scan that field for odd data. Then edit in DBA to correct.

Back to top

Tommy Johnson
Computer Consulting Services
IT systems support/software development

DBA Client (V2002.4 - 20 user)
Prime Technological Services
OEM Electronic Manufacturing Services

IP Logged

JamesTaylor

Browser

I love YaBB 1G -
SP1!

Posts: 8

Re: Data Access in MS Access 2002
Reply #2 - 10/27/03 at 16:29:04

Thanks Tommy,

Is there a way to clean the table up in Bulk? Whenever I export to a text file, I get errors, that is why I use Access. I never change the data, only link to the tables and look at the data through queries.

I have tried re-attaching to no avail as well.

I will try to take a look at the physical data tonight to see if I can spot something.

Thanks!

Back to top

IP Logged

aricon

Active Member

Systems
Consulting-See
website for
products!

Posts: 1283
Gender: male

Re: Data Access in MS Access 2002
Reply #3 - 10/27/03 at 17:33:28

Instead of doing a link table, do it as a Pass-Through Query which you should then funnel to a Make-Table Query and then run your specific query off the the new table in Access that results from the make-table query.

This has gotten us around almost all of the screwy data dictionary problems in DBA's non-standard data dict. formats.

Back to top

Sincerely,

Lorne Rogers
President
Aria Consulting & Implementers Ltd.
Phone: (780) 471-1430
Fax: (780) 471-4918
E-mail: lrogers@ariaconsulting.net

IP Logged

JamesTaylor

Browser

I love YaBB 1G -
SP1!

Posts: 8

Re: Data Access in MS Access 2002
Reply #4 - 10/29/03 at 15:27:28

Thanks!

What eventually fixed it here was to replace all of the "File*" files except for the FILLOC files with the new ones off of the CD and then destroy and rebuild the ODBC drivers.

Everything looks great after that.

Thanks for your suggestions and patience.

James A. Taylor Jr.
http://www.AICompany.com
web design, web hosting and search engine optimization

Back to top

IP Logged

heliosquare

Full Member

Posts: 121
Gender: male

Pass-Through query failing
Reply #5 - 02/24/04 at 13:36:54

Quote from aricon on 10/27/03 at 17:33:28:

Instead of doing a link table, do it as a Pass-Through Query which you should then funnel to a Make-Table Query and then run your specific query off the the new table in Access that results from the make-table query.

This has gotten us around almost all of the screwy data dictionary problems in DBA's non-standard data dict. formats.

Trying to do a Pass-through Query on BKARHIVL.B but the query is failing as Access is not able to dertermine what table I am trying to query.
Code:

SELECT BKARHIVL_B.BKAR_INVL_INVNM, BKARHIVL_B.BKAR_INVL_PQTY, BKARHIVL_B.BKAR_INVL_PCODE, BKARHIVL_B.BKAR_INVL_PDESC, BKARHIVL_B.BKAR_INVL_PDISC, BKARHIVL_B.BKAR_INVL_PPRICE, BKARHIVL_B.BKAR_INVL_PEXT
FROM BKARHIVL_B
WHERE (([BKARHIVL_B]![BKAR_INVL_INVNM])>129646)
ORDER BY BKARHIVL_B.BKAR_INVL_INVNM;

The above query gives me this error msg:
ODBC--call failed
Syntax Error: SELECT ...........FROM (#0)

How do I get it to realize what table I want?

Back to top

IP Logged

heliosquare

Full Member

Posts: 121
Gender: male

Re: Data Access in MS Access 2002
Reply #6 - 02/24/04 at 14:05:39

Figured out my syntax error. Should have been:
Code:

SELECT
    BKARHIVL_B."BKAR_INVL_INVNM", BKARHIVL_B."BKAR_INVL_PCODE", BKARHIVL_B."BKAR_INVL_PDESC", BKARHIVL_B."BKAR_INVL_PQTY", BKARHIVL_B."BKAR_INVL_PPRCE", BKARHIVL_B."BKAR_INVL_PDISC", BKARHIVL_B."BKAR_INVL_PEXT"
FROM
    "DBA"."BKARHIVL_B" BKARHIVL_B
WHERE
    BKARHIVL_B."BKAR_INVL_INVNM" > 129646.
ORDER BY
    BKARHIVL_B."BKAR_INVL_INVNM" ASC

Back to top

IP Logged

aricon Active Member Systems Consulting-See website for products! Posts: 1283 Gender:	Re: Data Access in MS Access 2002 Reply #7 - 02/24/04 at 23:15:09 You know....the questions where someone answers their own question are the BEST ones..... (Really easy, ya know....LOL!!)
Back to top	Sincerely, Lorne Rogers President Aria Consulting & Implementers Ltd. Phone: (780) 471-1430 Fax: (780) 471-4918 E-mail: lrogers@ariaconsulting.net IP Logged