Access RUN time as frontend to SQL Server how?

[TruePegasus]

I have a SQL server database on a server. Multiple PCs with Access Run time and one PC with Access developer. Have tried ODBC and all working on developer PC but do not know how to transfer the ODBC stuff to the other Run time only PCs. What is the recommended approach?

[zmbd]

When yu say you've "tried ODBC" what do you mean exactly?

[TruePegasus]

Hi,
I did not know anything about using access as front end to SQL Server so I looked on the internet and ended up using "linked tables".
When I look at the properties of a table it shows:

ODBC;DSN=MISLink;Description=MIS_Data_Link;APP=Mic rosoft Office 2013;DATABASE=MIS;QueryLog_On=Yes;QueryLogFile=C:\ MIS_QUERY.LOG;TABLE=dbo.M_Provider

in the description field.

I guess I would like to scrap what I have done sofar and just dop it the "right" way, whatever that is, so that I can develop on a PC with Office PRO (incl Access) and run the front end on PCs with just Office Home installed and using the free Microsoft Access run time module.

[Seth Schrock]

There are two methods to connect to SQL Server from Access and both use the ODBC drivers with linked tables. One is to use DSNs as you have done. The other is to use a DSN-less connection. Neither one is a bad way to do it. Using DSNs is easier to get things setup when you are creating the database, but then you have to put that DSN on each computer that you want the database to be on. DSN-less connections require more work to link that tables, but then nothing is required on the other PCs for the database to work. It is really up to you as to what you want to do.

If you choose the DSN-less connection method, I have written an article with some code already written to make it easier to attach the tables. See Managing DSN-Less Connections. If you choose to stay with what you have, then we can help with that too.

[TruePegasus]

Hi Seth, I had a look at your code and decided I would for now stay with my current method.
Can you guide me to some document please that explains how to copy the DSN to another computer?

[Seth Schrock]

I don't know of any document off hand, but I can certainly help you. Do you know if the systems you want the database on are 64 or 32 bit? If you have some 64-bit systems, are they running 32 or 64 bit version of Office? I actually written a vbscript file to automatically add a system DSN (available to all users). My script is currently setup for a mix of 64 and 32 bit systems that are all running 32-bit Office. If your environment matches this, then I can share the script with you.

[TruePegasus]

Hi Seth, All our machines are 64 bit but it's the 32 bit version of office. A vb scvript would be very C:):)L.

[Seth Schrock]

Just to let you know, my script uses a trusted connection so the Windows user information is passed onto SQL Server to verify permissions.

Open Notepad and go to File>Save As. Change the file type from Text File to All Files and then enter the file name that you want followed by .vbs to change it to a vbscript file. Here is the code that you need in it:

HKEY_LOCAL_MACHINE = &H80000002
strComputer = "."
 
Set oReg = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")
 
 
strKeyPath = "SOFTWARE\Wow6432Node\ODBC\ODBC.INI\EmployeeDatabase"
oReg.CreateKey HKEY_LOCAL_MACHINE, strKeyPath
 
oReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, "Driver", "C:\Windows\system32\SQLSRV32.dll"
oReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, "Description", "Your Description"
oReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, "Server", "SQL Server Name and Instance"
oReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, "LastUser", "DatabaseUser"
oReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, "Database", "Database Name"
oReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, "Trusted_Connection", "Yes"
 
oReg.SetStringValue HKEY_LOCAL_MACHINE, "SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources", "Your Description"

At the end of the last seven lines is what you need to change.

Also note that this is a system DSN which is different than a User DSN. If you are using User DSNs then you would need to change the registry path. I don't know what that is off hand.

[TruePegasus]

Hi Seth, thanks very much for that.
I now need to digest and try out.
:)