none
Scripting Microsoft Access import ODBC settings RRS feed

  • Question

  • Hello this is my first question on your forum. We have ODBC database that all our users have to get access to. The normal method of accomplishing this is:

    1) Run Microsoft Access as Admin. 

    2)create a blank database 

    3) right click table1 -> Import -> ODBC Database

    4) select "Link to the data source by creating a linked table -> ok

    5) Select "Machine Data Source" tab -> select "New"

    6) in the "Create New Data Source" window, select System Data Source radio button - > next

    7) select the SQL Server driver -> next -> Finish

    8) in the "Create a New Data Source to SQL Server" window enter ".....the name of our data source" in the name field and enter "....ServerName" in the Server field -> next

    9) where it asks "How should SQL Server Verify the Authenticity of the login ID?" select the radio button with "With SQL Server Authentication using a login ID and Password entered by user." -> make sure "Connect to SQL Server to obtain default settings for the additional configuration options" is checked -> supply login ID and Password -> next

    10)Check the  box with "Change the default Database to:" -> next ->finish

    11)Test Data source to make sure it is working correctly and click OK

    12)Select Ok  on the "Select Data Source" window

    13)Supply Login ID and Password to the "SQL Server Login" window -> ok

    14) and then we can cancel out of the "Link Tables" window and Access all together.

    We have a short cut that takes our users where we want them to go but first we have to manually go through this process that seems like it could be scripted. I used regshot to try and capture what registry files it changes and I found  4 values that change in 'HKLM\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\' such as Database, Driver, Server, and Lastuser. and 1 value at 'HKLM\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\ODBC Data Sources that contained a Name value. when I manually go and try and find these they are not there. and when I run something like 

    New-ItemProperty -Path 'HKLM\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\ODBC Data Sources'-Name DataSourceNameExample -PropertyType String -Value 'SQL Server' -Force

    I get the error saying it cannot find path .... because it does not exist. 

    Is there an easier way to go about this or am I missing something?

    Monday, January 6, 2020 7:03 PM

All replies

  • Sorry but this is not the correct forum for MSAccess. You will have to post in the MSAccess forum for your version.


    \_(ツ)_/

    Monday, January 6, 2020 7:52 PM