transferDatabase Method Problem

[neilgraham]

I am trying to dynamically link to a table in Sage Line 50.
When i use

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=SageLine50v9", , "INVOICE", "INVOICE"

It works but asks the user to choose a unique record identifier. I would like to do this automatically. Is there a way round this. I could find no reference in help.

 

[neilgraham]

TransferDatabase Method

Thanks for the reply.

My problem is that if i have the link to the table there on startup it asks all my users to log in to Sage. And sometimes it appears that if they do not have the correct sage permissions (because of security in sage stopping unauthorised users seeing Invoice details) then it caused Access to mis behave in strange ways.

Basically i am trying to read (read only) invoice details from Sage to populate a table in Access. Then a form linked to the order table will show certain users details of invoice amounts and dates etc. and ask them to raise an invoice if it has gone past a certain date.

I wanted to dynamically open the link to sage (based on which user is logged on, easy enough) and then close it at the end of the session (also easy enough) but as you point out the lack of an index causes this pain. The reason for this is that the basic front end containing all the forms and queries is copied to all users when ever an update to the front end is made. At least once a week. I do not want to get to the stage of having to maintain two separate front ends to achieve this.

So if i use File Get External Data, creating the link will still have to have user input.

I did wonder about creating the link in another access database and linking to it but this does not work.

Any more ideas.

Thaks

Neil

 

[neilgraham]

TransferDatabase Method

I see what you are saying about only asking for log in when you access the linked table. I think however that Access refreshes all links when it starts and so it finds the Sage one and asks you to log in. Even a db with no code in does this if the sage linked table is present.

I'm not an expert at Access security so do not know how you achieve the log in for other people.

I think i need to populate the Access table with the sage info once a day myself and let the users see that rather than the live data. Whic is a shame really and a pain if I am not in

Thanks

Neil

 

[neilgraham]

TransferDatabase method

Ok i will have to investigate more here.

I think that there is a startup code that runs behind the switchboard form that refreshes all links to the backend dbase. I could be mistaken as i did not design it.

But if what you say is correct it must be hidden in there.

Thanks


Neil

 

[neilgraham]

- As an alternative, you can "hard" link the table by specifying that the password should be saved. To do this, remove the existing link. Then relink using Files/Get External data. At some point in the dialog, you will see a checkbox that asks if you want to save the password. Check yes. That will prevent prompting when the table is later opened. If you decide to do this, make sure that you are logged on with an account that does NOT allow updating. You don't want the user to accidentally update the linked data.

Thanks Pat this did it for me. It stops the message coming up just as required

As for your warning Sage does not allow updating in this manner so it is perfectly safe.

Regards


Neil