Can I automate SQL-Server authentication?

[DaveB]

I Hope this is the right place to ask this question.

I have an Access Database which fetches information from a SQL database View. In order to do this I have had to set the user up with an ODBC DSN setting which establishes the link. I then call an Access Update Query which goes into the SQL view and pulls data from it. When this happens however it always prompts for the SQL Server Login details.

Is there a way to have this authentication screen bypassed, maybe by passing information to it from an Access Query automatically?

Thanks in advance for any help/suggestions.

Dave.

 

[KenHigg]

Something like this should work. You can google 'connect string' to disect it and plug in your own parameters.

strConnect = "ODBC;DSN=PDB2;UID=MMO2P001;PWD=MMO2P001;MODE=SHARE;DBALIAS=PDB2;TXNISOLATION=1;"

Set td = db.CreateTableDef("myNewTableName")
td.Connect = strConnect
td.SourceTableName = "myTableNameToConnectTo"
db.TableDefs.Append td

 

[DaveB]

Cool, thanks for the prompt reply, I'll check it out.