9 Replies - 411 Views - Last Post: 21 March 2019 - 11:27 AM Rate Topic: -----

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,203
  • Joined: 12-January 10

linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 08:14 AM

quick backstory:
as long as ive been programming i've never had to use access..so call me access dumb.

what i am tasked with doing is moving an access database to sql server (done)
i have linked the tables according to ms documents and the tables show up on the access table schema

the reason for this:
the client wants to use their access front end but sql back end for performance ---- i could talk all day about this but lets stay on point

so i have done all the linking etc.. when i open up a linked table it does not allow me to edit the records in any form.

the questions:
do i need to remove the original access tables from access?
how do i know the linked tables are working?
how does access know to use the access front end the client made?
is there a quick and dirty way to test any of this?

i havent found any documentation on how to do any of the above...


thanks

Is This A Good Question/Topic? 0
  • +

Replies To: linked tables from access to sql-- how do you know it is working

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 08:18 AM

That's some hell.. we have an individual, thankfully I am not direct support, that has enough clout to make the demand to keep the "Access as is" even though we ported that db to our SQL server..

These steps seem about right. I would highly suggest making a wholly independent test Access db to work through the steps of setting it up before doing it on the original form. Bonus points if you can copy over the form and just archive the original.

https://www.quackit....access_2016.cfm
Was This Post Helpful? 0
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,203
  • Joined: 12-January 10

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 08:23 AM

i made an access back up and an sql back up and testing through that. if you would see how badly spelled some of these things are you would shoot yourself. also i suggested creating a brand new application for this and i was told nope.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 08:26 AM

Yuuuuuuuup.. sounds like a typical Access-holdout.

Super glad I am secondary back up on that thing and only have to fake knowing what's up. ;)
Was This Post Helpful? 1
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,189
  • Joined: 12-December 12

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 08:33 AM

do i need to remove the original access tables from access?
You have a backup ;). Rename them initially if you prefer, they are no longer in use because you have migrated all the tables to SQL Server; the data exists in SQL Server - check this!

how do i know the linked tables are working?
There is the Linked Table Manager on the External Data tab to Refresh and Relink. Changing a record and checking this change in SQL Server is a good step.

how does access know to use the access front end the client made?
If this is all in a single Access database file then the forms should be pointing at the linked tables. You can check this in the Form properties, or, again, open a form and change a value.

is there a quick and dirty way to test any of this?
Editing and adding stuff ;)
Was This Post Helpful? 1
  • +
  • -

#6 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,203
  • Joined: 12-January 10

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 09:10 AM

i tired doing an edit on access on the linked table but it says the record set is not updateable..


another question

how do you link all the queries they have in access?
(they have hundreds)
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,189
  • Joined: 12-December 12

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 09:18 AM

I don't know what steps or process you took to upsize to SQL Server but I would have thought that the queries would then reference the linked tables and still work.

Sorry, I've exhausted my memory of this subject, it is years since I touched Access. Although... is there more to the message than just "not updateable"? Is the update trying to update more than one table? Are primary and foreign keys (and identity keys) all functioning as they should? Can you edit the row from SSMS?

Anyway, maybe someone else will come along ;)
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 09:18 AM

The last I remember - you have to go through everyone and check the 'pass through' and give it a connection string, but again.. super rusty on it.

Quote

On the Query menu, point to SQL Specific, and then click Pass-Through.
On the toolbar, click Properties to display the property sheet for the query.
In the query property sheet, place the mouse pointer in the
ODBC Connect Str property, and then, click the Build (...) button.


info: https://support.micr...query-in-access
Was This Post Helpful? 0
  • +
  • -

#9 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,203
  • Joined: 12-January 10

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 09:36 AM

after trial and error which is the best way to learn and hopefully not screw something up to bad i had to do the following:

add primary key to sql tables which he doesnt have in access
and the queries must be manually updated to point to the sql tables


in other words i hate access
Was This Post Helpful? 0
  • +
  • -

#10 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7471
  • View blog
  • Posts: 15,493
  • Joined: 16-October 07

Re: linked tables from access to sql-- how do you know it is working

Posted 21 March 2019 - 11:27 AM

View PostDarenR, on 21 March 2019 - 10:14 AM, said:

as long as ive been programming i've never had to use access..so call me access dumb.

I'd call you lucky.

View PostDarenR, on 21 March 2019 - 10:14 AM, said:

do i need to remove the original access tables from access?

Yes. You want them to use the links. The link name should be identical to the old table name.

View PostDarenR, on 21 March 2019 - 10:14 AM, said:

how do i know the linked tables are working?

You should be able to open them up under tables as if they were, well, tables.

View PostDarenR, on 21 March 2019 - 10:14 AM, said:

how does access know to use the access front end the client made?

Unclear on this one. Your client should NOT have rogue Access files floating about. You should have the final version with all the links working. It should be easy for you to test functionality because you'll have the thing in front of you.

Having had the misfortune of doing this kind of thing, I have some canned code that might help:
Sub MakeLink(db, connStr, srcTable, dstTable, pkFields)
    Dim tDef
    Set tDef = db.CreateTableDef(dstTable)
    tDef.Connect = connStr
    tDef.SourceTableName = srcTable
    db.TableDefs.Append tDef
    db.Execute "CREATE UNIQUE INDEX [Pk" & dstTable & "] ON [" & dstTable & "](" & pkFields & ")"
End Sub

Sub LoadLinks()
    Const cs = "ODBC;Driver={SQL Server};Server=YOUR_SERVER_NAME;Database=YOUR_DB_NAME;Trusted_Connection=yes;"
    Dim db As DAO.Database
    Set db = CurrentDb()
    
    MakeLink db, cs, "dbo.Customer", "Customer", "CustomerId"
    ...
End Sub



Why use code? Because you want your link self contained on not associated with the user's ODBC or OLEDB registry entry. Because if you have more than a handful tables, using a GUI is both painful and hard to reproduce if you need to tweak things.

Hope this helps.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1