ODBC

[deb]

Access 2003

I have an ODBC connection to Lotus Notes.
I did File> Get External Data> Link Tables to create a link to the
b_Claim_Data_Projects table in Lotus Notes.


I use the ODBC linked table in the MakeTable query called
qNonConformanceMkTbl to create a replica of the data in a table called
tNonconformance. By creating a MakeTable replica, I don't accicentally
edit/delete data in Lotus Notes (been there, done that)

I have a button to run a MakeTable query in my SplashForm.
Now the issue...

I would like other users to be able to click the button and run the
MakeTable Query.
But since Lotus Notes uses userID and passwords it fails.

I have save password selected but it does not help the other users.
Is there a way it can read the users Lotus Notes INI file(for username) and
prompt for their password?

I am open to any other suggestion.

 

[Tom Wickerath]

Hi Deb,

I have a button to run a MakeTable query in my SplashForm.

Rather than using a MakeTable query, you might want to consider using an
Append query to append (add) records to a pre-existing table. This way, the
table can have a primary key and have whatever other fields indexed
appropriately. You can also control other field properties, such as
AllowZeroLenght for text and memo fields. Use code to delete all records from
the table, then run your append query.

You can even use the idea of a temporary work database, to help avoid bloat
in your FE application file. Here is a link to a zipped sample on my web
site, if you are interested:

http://www.accessmvp.com/TWickerath/downloads/tmpwrkdb.zip

When running an action query in code, I recommend using the .execute method
instead of DoCmd.RunSQL. This way, you avoid the warning messages (and the
extra lines of code to Set Warnings False followed by Set Warnings True).

Example:
CurrentDB.Execute "qappLotusRecords", dbFailOnError

Is there a way it can read the users Lotus Notes INI file(for username) and
prompt for their password?

Sure, if you know where this .ini file is located. I have an example of
reading a text file in a sample that you can download from the Seattle Access
User's Group:

http://www.seattleaccess.org/downloads.htm

Compilation of Tools by Tom Wickerath, October 2006 --
Download (656 kb) slides

If you choose to look at this sample, read the Word document named "Seattle
Access Presentation 10-10-2006.doc" first. Then open the database named
RearrangePFAFile.mdb. The code to open a text file, in this case a file with
the extension of .pfa, is found in the RearrangePFA subroutine, in the module
named "basRearrange". You will need to use similar code to read one line at a
time, until you come to some keyword that you can use to denote the Username.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[deb]

Thank you for taking time to enter this information. Going over this will
keep me busy and teach me somthing I have been wanting to learn. Your work
is an inspiration and thank you for sharing!!

 

[Tom Wickerath]

You're welcome!
And Thank You for the kind words.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________