Linking Access to SQL server

[johnseito]

Hello,



Have anyone ever link Access to SQL server. And can you share some methods or suggestions.

Access is use as front end while SQL-server is use as back end.

Please advise !

Thanks

[JoeM]

I did a few of those a few years back.

Via a simple Google search: https://www.quackit.com/microsoft_ac...ccess_2016.cfm

[ssanfu]

You might also look at the clips by Steve Bishop:

Lesson #6
Creating an ODBC connection to SQL Server

Lesson #7
Linking Tables from an ODBC connection to Access

[isladogs]

An alternative approach involves using DSN less connections.
This is possibly a little more tricky to setup but it avoids the need to setup the ODBC connections on each workstation running the Access FE

For more info, see https://support.microsoft.com/en-gb/...nked-tables-in

[johnseito]

I noticed that when I link access to sql server I need to save a data source name file some where on the share drive for other people to access it too, otherwise if is only save on locally only I could access it, other people can’t.

How can we not use data source name and still have other people access it ?

[johnseito]

Isladdogs, how do I set this up then, please advise ? Thanks

[johnseito]

I want to link it where if another users doesn’t have access to the database can still see the linked data and use the program. How do I do that ?

[isladogs]

John
I'm not sure how many of your last 3 posts were aimed specifically at me.
Regarding DSN less connections, did you read the link I provided?
Here is another guide that you may find useful http://www.accessmvp.com/DJSteele/DSNLessLinks.html.

However I haven't responded to your other points as I'm not really sure what you are asking and whether its variations on the same question.
Perhaps you could explain again and you may then get more than one reply to your question(s)
Thanks

[johnseito]

John
I'm not sure how many of your last 3 posts were aimed specifically at me.
Regarding DSN less connections, did you read the link I provided?
Here is another guide that you may find useful http://www.accessmvp.com/DJSteele/DSNLessLinks.html.

However I haven't responded to your other points as I'm not really sure what you are asking and whether its variations on the same question.
Perhaps you could explain again and you may then get more than one reply to your question(s)
Thanks

Thanks, I am going to do some investigation and look into your links both of them further and see what kind of traction I get. I think is best to do DSN less linking because it helps with user on other machine.

[isladogs]

For the past 15 years or so, I have only used DSN less connections to Access or SQL Server BEs as it saves a lot of work overall.
Once you have set it up the first time its simple to use/maintain.
However most developers stick with ODBC connections possibly because the initial setup needs more work/code

[johnseito]

For the past 15 years or so, I have only used DSN less connections to Access or SQL Server BEs as it saves a lot of work overall.
Once you have set it up the first time its simple to use/maintain.
However most developers stick with ODBC connections possibly because the initial setup needs more work/code

Thanks for sharing the links and it’s great that you have 15 years, wish I could say the same. So I tried your first link and unfortunately it doesn’t connect to the sql server database.

What should I put for local and remote table name ?

I noticed if you put local table name it finds that table in Access and deletes it, not sure why it does that.

My connection is trust connection.

And I don’t know what is dbAttachSavePWD it came with a number, not sure where it came from.

I get error message at currentdb.tabledefs.append td.

Stated connection failed, sql server error: 772. Please advise ! Thx

[johnseito]

What do you advise I use, CreateTableDef or DAO.RegisterDatabase method ? Thx

[isladogs]

Hi
Apologies but you're asking me questions that don't seem to fit the way I do things.
My fault for not reading the links properly first.
Which link are your questions referring to?
Answering on my tablet so I cant supply any code at the moment.
Apologies for a rather general answer which doesn't answer all your questions.
My approach uses two local tables
1. TblTableLnkTypes - this contains details of the connection strings for each link database - Access/SQL Server etc.
This includes database name/path/password for Access files and server name/instance/password etc for SQL Server.
Using this table allows me to connect the FE to one or more BE files. In one case, I have 7 different BEs though not all in use at once.
2. TblTableLinks - this lists all linked table names and aliases together with the link database
The table names are those in the linked database(s) - I don't use the dbo_ prefix
The relinking code then removes all existing links (if any) then loops through each linked table in turn making new links based in the information stored in the first table to build the connection strings.
I've not used trusted connections but the code works for those - the connection string is slightly different however.
For a large database with around 350 linked tables, the relinking takes less than 30 seconds
I can try to be more specific if needed when I have my code in front of me.
I hope this is some use in the meantime

[johnseito]

It definitely is useful. wow that is amazing, linked 350 tables in 30 seconds.

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

I was reviewing this link that you had provided. I was stuck in some areas of it. Some guidance and codes would be nice and appreciated.

[isladogs]

Less than 30 seconds...!

I'm busy most of today but will try & post some example code later or tomorrow
It won't be a complete working example as what I have is part of large commercial apps but hopefully it will be more than enough to get you going

If possible can be quote the part of Doug's article that you are stuck on. Do bear in mind that it was written around 2003 or so