Help Request PLEASE =8^0 ODBC connection failed sql tables

  • Thread starter Thread starter Maureen
  • Start date Start date
M

Maureen

Hello all,

I am struggling to reconnect an Access97 mde file from our EDI application
to a new sql database. It has been working without error for years as is. We
have just replaced the back end from access to sql.

I have created a system DSN and manually linked the tables removing the dbo_
Of the 12 linked tables 10 open fine and querys etc realted to them are
operating just great. The other two however get ODBC Call failed error
trying to open the tables they then open and are filled with #NAME?

All of the links were done at the same time with the same DSN 10 of 12 work
what gives here?

Any help would be greatlly appreciated!

M
 
Access needs to detect a PK value on the Server.
If it can't then you get #Name? in many cases.

When Access does not detect it correctly it sometimes asks you to identify a
Unique Record manually.
If you fail to do so you can still fix the problem by creating a
pseudo-index in Access.

You can create a "Fake" index in Access that tells Access which columns make
a Unique record in the linked table. The index is only used by Access is
completely unknown to the Server.

The following example creates an index on an ODBC linked table. The table's
remote database is unaware of and unaffected by the new index. The following
example won't work if you simply paste it into Microsoft Access; you first
need to create an ODBC linked table called OrderDetailsODBC.

(CREATE INDEX Statement in Help has some examples)

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);
 
Joe,

Many many thanks for your reply.

Sorry that I am very green with code... I understand tables, queries, forms
and reports pretty well but code is a bit Greek to me. The ODBC linked table
called OrderDetailsODBC, is it to be created as a table is the sql database
and linked with the others? What fields or data does it need to contain?
After this do I actually just past the code into a new module and run it to
create the fake index?

I have over 1,000 lines of orders in the EDI app from overnight and hope to
get the "stuffed in this AM" rather than print them out and re-key again..
like yesterday. There are two of us trying to make this work on our own
rather than get an expert in as we have no$$ left. It seems to me that we
should be able to do this since we got al of our other databases working
fine. The only difference is this is Access97 on Win98 all the others are
Access2K on W2K.

M
 
Joe,

I have added the table in sql and created the module with the correct
spelling etc excatly as you have listed but I have abut I am getting a
"SYNATX ERROR" and it highlights the entire line any suggestions?


Sub test_Code()
CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);
End Sub

M
 
Create a query and paste the code in as a SQL command.
Then run the query.
CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);

Change the index name OrderID to something in your app.
Change OrderDetailsODBC to your linked table name.
Change the PK field from OrderID to the PK in your linked table.

If it takes more than one field to uniqeuly identify a record use:
CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (field1, field2, field3);
 
Joe,

Thanks for your reply and PATIENCE with my lack of understanding. Here are
the short points:

Twelve tables ODBC linked on two separate workstations
#1 W2K with Access2K all twelve work fine
#2 Win98 with Access97 ten work and two do not. This is the problem

I added a table (OrderDetailsODBC one feild OrderID AND no data in it) to
the sql database and ODBC linked it - it asked me for the unique field when
linked.

I pasted CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID); And got
"Primaty Key Already Exists" message The problem still exists

I thryed the same proceedure on the problem table:
I pasted CREATE UNIQUE INDEX ID ON Inventory (ID); And got "Primaty Key
Already Exists" message The problem still still exists.

Thanks again for any suggestions and help you can provide.

M
 
Looks like my suggestion did not help in this case. Sorry.

But you have already identified the real problem: <g>
#2 *****Win98***** with Access97 ten work and two do not. This is the
problem

Why not just give up and upgrade the workstations to a real OS?
 
Joe,

Upgraded to Access2K al all is good.
I am not able to move to a "real" o/s since I have some very old harware
'hardcoded' into the EDI app. many thanks for your willingness to assist.

M
 
Back
Top