Sql Server Migration Assistant - Front-End Access

[basshead22]

Hello All,

I have a database that has been split with forms , queries , reports etc.. in the front end and just the tables in the back end file. I've decided to move the back end to SQL Server 2016. I used the SSMA tool to copy the tables to the SQL server and link the tables to my front-end. Everything worked great on my computer i've noticed an increase in speed as i was hoping. The only problem i'm having now is when i take the new front end that has been linked to SQL server and copy to another users PC ... it won't establish the ODBC connection.

I've tried creating the ODBC connection DNS file on the other users PC but i would have to re-link all the tables again.... in order for it to work. I have 300+ users using this database and it would be very painstaking to have to do this process 300+ plus time.

Does SSMA create its own ODBC DNS file that i can just copy to the other PCs? Ive tried to see if i can find some type of file it has created but no luck.....

Any help would be appreciated.

 

[pbaldy]

2 methods I've used are linking with a File DSN stored on the server and DSN-less connections like:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

 

[Ranman256]

you should only need to relink 1 FE, then distribute it to all.

 

[pbaldy]

you should only need to relink 1 FE, then distribute it to all.

If you link using a system or user DSN, that DSN would have to exist on each client machine.

 

[isladogs]

But if you use DSN less connections and the default SQL Server driver installed with Access, you won't need to do any additional setting up on each PC.

 

[pbaldy]

I guess I didn't say that clearly enough.

 

[deletedT]

But if you use DSN less connections and the default SQL Server driver installed with Access, you won't need to do any additional setting up on each PC.

But still the main problem remains which driver to use for DSN less connections. I think you remember my question.
I'm still wondering which driver to use. To be true, it's hard to understand which one is going to be continued on next versions of Access. I've read a lot of articles from Microsoft and still wondering which one to choose.

 

[isladogs]

Hi tera

First of all, I'm really no expert and my knowledge of SQL Server is getting hazier than in the past when I was more actively involved in setting this up with clients
I'm still using the default SQL Server driver supplied with Access as its always been sufficient for both my needs and those of my clients.

A typical connection string used with one of my apps is:
ODBC;DRIVER={SQL Server};SERVER=.\SQLEXPRESS;DATABASE=SDABE;APP=SchoolDataAnalyser;UID=******;PWD=*******

However, over the years, I've also used SQL Native Client and dabbled with Trusted Connections. I can't honestly remember why I stopped using these.

I'm sure I would have upgraded to a newer driver if i'd ever had a pressing need to do so. I really don't know what the main reasons are for upgrading.
New features I couldn't live without? Improved security? Other forum members are probably better placed to advise you on this

If there was a good reason to upgrade, I would have arranged for newer drivers to be installed on each client workstation by network admins. I still wouldn't have got involved with setting up individual workstation myself.

 

[basshead22]

2 methods I've used are linking with a File DSN stored on the server and DSN-less connections like:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

Do you have any links to the other option as-well? File DSN on the server?

 

[basshead22]

If you link using a system or user DSN, that DSN would have to exist on each client machine.

I definitely don't want to do that! still trying to figure out how to go DSN-less.

 

[basshead22]

But still the main problem remains which driver to use for DSN less connections. I think you remember my question.
I'm still wondering which driver to use. To be true, it's hard to understand which one is going to be continued on next versions of Access. I've read a lot of articles from Microsoft and still wondering which one to choose.

Hi Tera, just curious what code are you using to do your DSN-less connection ? and does it work for you? I'm still having trouble getting it to work ....

 

[isladogs]

Basshead
Are you able to adapt the connection string I posted earlier?

 

[basshead22]

Basshead
Are you able to adapt the connection string I posted earlier?

Going to try it out now isladogs... but i have a few questions...

Can i use the SQL Server migration assistant to upload and link my back end tables to the SQL Server? then use this code to distribute to others that use the front-end? Or is it best that i import and connect the tables manually with a DNS connection first? and indicate what my indexes are?

Also is the code below where i put my database name , servername? i want to use trusted connection also so should i delete the place holder text "UID" and "PWD" and just leave it completely blank? sorry if these are dumb questions i'm fairly new to coding in access.

strConnectionString = "ODBC;DRIVER={sql server};" & _
"DATABASE=" & DatabaseName & ";" & _
"SERVER=" & ServerName & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PWD & ";"

 

[isladogs]

Going to try it out now isladogs... but i have a few questions...

Can i use the SQL Server migration assistant to upload and link my back end tables to the SQL Server? then use this code to distribute to others that use the front-end? Or is it best that i import and connect the tables manually with a DNS connection first? and indicate what my indexes are?

If you have A2010 or earlier you can also use the Upsizing Wizard in Access
However that was deprecated in later versions.
However SSMA is a more powerful tool so I would use that anyway.
There is also the SS Import/Export Wizard.
Take a look at both as they have some overlaps but I haven't used either for several months so check for yourself
IIRC, using the free SS Express version you cannot save your settings with the Import/Export wizard but otherwise it works well

Bear in mind that some of the 'new' datatypes like MVFs, calculated fields, hyperlink fields cannot be upsized but you will be advised of issues and action needed

If you want to go DNS-less, you can forget about DNS connections
Don't understand comment about indexes

Also is the code below where i put my database name , servername? i want to use trusted connection also so should i delete the place holder text "UID" and "PWD" and just leave it completely blank? sorry if these are dumb questions i'm fairly new to coding in access.

strConnectionString = "ODBC;DRIVER={sql server};" & _
"DATABASE=" & DatabaseName & ";" & _
"SERVER=" & ServerName & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PWD & ";"

Yes - enter your connection details in that string
For trusted connections you need something like this:

 strConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
'                                    "Server=" & MyServer & ";" & _
'                                    "Database=" & MyDatabase & ";" & _
'                                    "Uid=" & MyUsername & ";" & _
'                                    "WSID=;" & _
'                                    "Trusted_Connection=yes;"

but I can't explain the details as its at least 8 years since I used that code
The best reference source by far is https://www.connectionstrings.com/sql-server/

Hope that helps get you started

 

[basshead22]

If you have A2010 or earlier you can also use the Upsizing Wizard in Access
However that was deprecated in later versions.
However SSMA is a more powerful tool so I would use that anyway.
There is also the SS Import/Export Wizard.
Take a look at both as they have some overlaps but I haven't used either for several months so check for yourself
IIRC, using the free SS Express version you cannot save your settings with the Import/Export wizard but otherwise it works well

Bear in mind that some of the 'new' datatypes like MVFs, calculated fields, hyperlink fields cannot be upsized but you will be advised of issues and action needed

If you want to go DNS-less, you can forget about DNS connections
Don't understand comment about indexes



Yes - enter your connection details in that string
For trusted connections you need something like this:

 strConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
'                                    "Server=" & MyServer & ";" & _
'                                    "Database=" & MyDatabase & ";" & _
'                                    "Uid=" & MyUsername & ";" & _
'                                    "WSID=;" & _
'                                    "Trusted_Connection=yes;"

but I can't explain the details as its at least 8 years since I used that code
The best reference source by far is https://www.connectionstrings.com/sql-server/

Hope that helps get you started

Would you happen to know why i'm getting this error when i try to select the Microsoft DAO 3.6 Object Library reference as instructed in the link you posted about going DSN-less?

"Name conflicts with exisiting module, project, or object library.

I haven't selected any other references other than the default ones. See images.

 

[isladogs]

The DAO reference is very old and has been replaced by the Office xx.0 Access database engine object library. As that includes the DAO functionality, you can't have both and should use the new reference which is included by default