SQL data source credentials error

[dtdukes86]

Hi all its been a long time since my last post but ym new projects has forced me to seek a little help if anyone can offer any.

so i have acces 2016 front end and backend is SQL server. All connections work and data is being saved via LAN etc .

The data connection is via OBCD 64bit and works great hoever when you 1st login to front ewnd you need to enter SQL pasword, in previous projects there was a small save password box when creating the external data links but for some reason this isnt there any more, ive added a few lines into registry after follwoing some online tuttoirals and this does give me the lastuser .. so we dont have to type the user name any more but for some reason i have to type the sql password every time the application closes and re-opens and i cant for the lfie of me get around this. im sure there is a better way but it bugs me that i cant get it working.

 

[arnelgp]

you can backup your front end.
delete all linked table to sql and linked it again.
make sure you "save the password" when asked by the wizard.

 

[dtdukes86]

hi i did ask you suggested but the "save the password" check box is no where to be seen , i have done this before in earlier versions of access but for some reason its gone in this version 2016

 

[Minty]

Not that at that step - when you go to actually link the tables:

 

[dtdukes86]

can u provide a details how i get to this diaglog box, i have seen it before but simply cant find it on 2016 ;(

 

[Minty]

External data tab:

 

[dtdukes86]

I Found the little box but im not sure how to remove all the links and recreate them easily . i used the access migration tool originally to perform the split and links for me

 

[dtdukes86]

OK so now i have this issue with some tables that didnt have keys in

 

[dtdukes86]

i dont need a key in some of the tables is there a way to get around this

 

[Minty]

If you don't need a key and don't specify one you may not be able to edit that table.

A simple method to add the password is to simply delete and save one table then, using the edit button on the Linked table manager, paste the new connection details into the other existing tables.

 

[dtdukes86]

hi guys i finally managd to fix this issue i kept the tables that didnt need a key on the local front end, as they are temp tables anyway for qrys etc. and the tables with keys stay on the sql server with the passoword saved as per your recomendations above, , i did run into a problem with my date fields not showing date picker so i had to update my sql driver which i tried 13 and 17 and both worked, howver this has led me to another issue which i cant seem to get around in every form it appears.

when i wasnt using sql driver 13 or 17 i could start a form in Add mode and enter my data , as soon i typed the 1st charector the primary key field would go from "new" to id = x ( 5 for eg) howver after updating the sql driver i dont get to see the primary key at all untill the form either loses focus or i click off the main form onto a sub form and back again. its hard to explain, i can see "new" when i open the form but once i start typing i get a black field until i finish the form and click elesewhere this is very annoyinng as i have event triggard vba code that uses the primary key suchas invoice ref number field take the primary key at certain parts of the form being filled in . this also makes saving the form in vba a pain too as i get a error basically saying it cannot be saved as the subforms dont yet have the primary key , which is still showing as blank . i ll try and get a couple screen shots to explain.

 

[Minty]

SQL Server doesn't display the inserted ID field until the record is committed, which is why it doesn't show until saved (The opposite behavior of an Access table).
You will need to commit the record (You can use Me.Dirty = False to force a save) before trying to use the PrimaryKey anywhere.

Your date issue is almost certainly caused by using DateTime2 data type in SQL server tables. Access doesn't really work very well with it, unfortunately. Stick with the old-fashioned DateTime data type for an easy life.

 

[dtdukes86]

INTRESTING!!