Just linked tables to new SQL Server 2008 db

[mapat]

Hello,
I have just finished importing an ACCESS db (back-end) to a SQL Server 2008 db. My front-end is ACCESS and the back-end is SQL Server. When I would fill in some txt fields on a formA, another formB is supposed to open. This used to work when the tables were linked to my old ACCESS db, but now that they are linked to my new SQL Server back-end it has stopped working. Can someone give me a hand on this?

Thank you very much

 

[SOS]

What ODBC Driver did you use to link to SQL Server 2008? You should be using the SQL Native Client (10.0) driver.

 

[mapat]

What ODBC Driver did you use to link to SQL Server 2008? You should be using the SQL Native Client (10.0) driver.

In ACCESS 2007, when I created the ODBC, it just said SQL Server in the "Create New Data Source" window. How would I know if that's the right driver?

Thanks

 

[SOS]

In ACCESS 2007, when I created the ODBC, it just said SQL Server in the "Create New Data Source" window. How would I know if that's the right driver?

Thanks

Well, that isn't the right driver because if you had the right driver it would say SQL Native Client (10.0) instead of just SQL Server.

See here.

 

[mapat]

Well, that isn't the right driver because if you had the right driver it would say SQL Native Client (10.0) instead of just SQL Server.

See here.

I have just finished creating an ODBC with SQL Native Client (10.0), relinked the tables using this new driver and it still doesn't work.

Thanks

 

[SOS]

I have just finished creating an ODBC with SQL Native Client (10.0), relinked the tables using this new driver and it still doesn't work.

Thanks

Okay, next part - when you say it doesn't work, what exactly is supposed to happen and how does it not work (error messages, just sits there, etc)?

Can you update any information in the tables directly?

 

[shadow9449]

Hello,
I have just finished importing an ACCESS db (back-end) to a SQL Server 2008 db. My front-end is ACCESS and the back-end is SQL Server. When I would fill in some txt fields on a formA, another formB is supposed to open. This used to work when the tables were linked to my old ACCESS db, but now that they are linked to my new SQL Server back-end it has stopped working. Can someone give me a hand on this?

Thank you very much

Do they all have a primary key? Access doesn't care but SQL Server does.

SHADOW

 

[mapat]

Do they all have a primary key? Access doesn't care but SQL Server does.

SHADOW

I don't think this is a problem caused by the program itself because it works fine when the tables are linked to the old back-end .mdb file, but as soon as I relink the tables to SQL, it stops working. Basically when you finish typing in info in a txtfield and based on other 2 values on the form, it would open a form or not. Those values are correct becuase I already checked them. Does anyone know about compatibility issues between ACCESS 2007 and SQL Server 2008? If so how would I resolve this issue?

Thanks again everyone

It just sits there and doesn't do anything. Yes, all the tables have primary keys.

 

[SOS]

When you linked the tables to the SQL backend, did a dialog box pop up asking you to choose what the primary key was?

If you go into SQL Server Management Studio, can you add records directly to the tables?

 

[mapat]

When you linked the tables to the SQL backend, did a dialog box pop up asking you to choose what the primary key was?

If you go into SQL Server Management Studio, can you add records directly to the tables?

Yes, there was 1 table that did not have a primary key but that's just a lookup table, and when SQL asked me to choose the primary key I did, so now all the tables have primary keys.

Yes, I just added a record to a main table using Management Studio.

Thanks

 

[SOS]

So it still will not let you add a new record directly in a linked table?

 

[mapat]

So it still will not let you add a new record directly in a linked table?

We misunderstood each other. The records are inserted into the tables as they should, so the main goal is fulfilled, but there is a little pop-up form which does some calculations that's supposed to show and it doesn't do it when the tables are linked to SQL Server.

Thanks

 

[SOS]

Okay, so the form pops up but the text box/boxes is/are blank, or do they show #Error or something?

Can you post a screenshot of the form in design view, showing the formula on the properties dialog and then in form view?

 

[Rx_]

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
A little over half way down:
For anyone upgrading to SQL Server 2008 dB -
install this on every workstation - then update the ODBC connection to the new
SQL Serever 2008 Native Client 10

Microsoft SQL Server 2008 Native Client 10

• • X86 Package (sqlncli.msi) - 4549 KB
    X64 Package (sqlncli.msi) - 7963 KB
    IA64 Package (sqlncli.msi) - 11112 KB
    
    Will try this instead of the huge hot fixes listed above for the Workstations. Yep! This is what you need to proceed.
  Microsoft SQL Server 2008 Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.
  
  Audience(s): Access 2003, SQL Server 2008, ODBC, Linked Table

 

[SOS]

9 posts and almost the same thing on each? Are you just going around and posting this everywhere that refers to an ODBC connection with SQL Server 2008? The thread was almost 4 months old.

 

[Rx_]

Sorry, I was on a mission to get this figured out.
After seeing how old the post was, treid to consolodate.
Will go back and clean up today.
Microsoft had recent updates, and none of the sites seemed to have the key information to solve the problem..