Linked table - linked to by ODBC using DNS - how to avoid DNS user name & password prompt?

If your users connect to the save server each time, then just build a small form where they enter the servername once, then store the data in a table, and then use that table to rebuild your connection strings each time.

I doubt they know the server name, and even if they do then I also need the database name and I doubt they know that either.  I think I will have to find out this info somehow though.

When you launch the application, open a connection to the database using the existing (not a new) DSN where you pass username and password. Once done, Access should keep this info during the session.

/gustav

Thanks Gustav - that's what I'm doing presently but even so the users are looking for a way to avoid having to provide the ODBC / DNS user name each time they start a new session.  Do you have any suggestions?

> .. that's what I'm doing presently ..
Not exactly. You wrote:

> I've tried to write code that creates a DSN each time ..

I suggest using the existing DSN.

/gustav

OK - sorry for not being clear on that.  The idea of creating a DNS each time is that you can theoretically pass the user id and password in code thereby avoiding the user having to do this.  BUT I'm getting problems not least of which is that I can only test with limited and infrequent remote access!  I'm thinking I'll need some technical support from Sage.

That's a good idea.

Still, I can't see why you can't pass username and (empty) password to a connection using the existing DSN.

/gustav

Thanks Gustav - yes you are quite right - that works.  However this gives me another problem:

I now have an ADO connection to the required table in SAGE which I can use instead of the ODBC linked table but it doesn't behave in the same way.  i.e. with the ODBC linked table I can use this as the source in a query to get just the data I want and it is still only in memory (which is as I prefer).

With the ADO method I can only think of populating a local table with the data from Sage, and then using that as the source in my query.  The issues here are that the table is huge, and the data is sensitive so I don't really want it in a local table.  It might be OK if I could fetch only the records I want but I don't think you can use a WHERE clause with a DNS connection. Do you agree?  If this is the case, then I can only think of using a non-DNS connection string in my ADO but I don't know any of the details for the connection string and so far my googling hasn't been successful on this front!

Thanks Gustav but I can't see how this will help.
I'm now thinking there must be a way to delete and re-create the ODBC linked table at session start-up and hope the link can then be opened without the prompt for user name and password.

> .. I can't see how this will help.

By passing user credentials for the DSN before the user opens tables of that DSN.
Did you try this?

/gustav

Yes, but that just mimics what you can do manually by deleting the linked tables and recreate the links in your app. Doing that, you will recreate the connection string and this time you will, of course, insert the user credentials.

/gustav