Connect to db via ODBC

Transact-SQL
1

Hello All,

I have some dbs that I connect to via ODBC in MS Access (linked tables). This works great however, Access at times cannot handle the big tables.

Is there a way to connect to the db via ODBC like I can in access....or maybe not the same was but maybe via import wizard or connection string....I have tried looking myself by have not come up with anything concrete.

Any Ideas?

cg

2

Short answer: Yes, you can connect to most databases using ODBC.

For sql server, here are some sample connection strings:

https://www.connectionstrings.com/sql-server-native-client-11-0-odbc-driver/

3

is the access app providing the front end UI for your users? how many users?

4

No not at this time. This would be for the report developer (me...he he)

However, in the future that may be an option.

Thanks

5

so have you looked into the possibility of using ssrd

6

Ok....that is not ringer a bell...what is it?

7

haha sorry meant to say ssrs a microsoft product for reporting purposes

8

That has crossed my mind....I have SQL 2014....I did look at the Reporting services config manager....would that allow me to connect to a system DSN...I imagine it would..

9

Access has a column limit of 255. If a table has more, shard it into several views and link those.

1 Like
10

Yeah...I agree....however...I wanted to go to SQL because the performance is better in SQL....I'm finding that it is not easy to just link to a db table like I can in access.....which is surprising that with SQL I can't do this easily.....I guess the search continues....thanks

11

Can you give a concrete example? LInking to a table in SQL is just a JOIN operation. Do you mean something else?

12

Here is the senerio:

We have some data that is on a Mainframe type system. This is a large company and there is a lot of data. Some of this data was set up on a DataDirect connection.

So in MS Access I can go to the external data table and click ODBC/ then Machine Data and choose the correct db from a DataDirect Shadow Client driver. Then connect to the appropriate table I need.

Well I would like to connect to the same DataDirect Shadow Client driver like I can in Access in SQL. Maybe there is not a way to do it....I don't know.

Thanks,
CG

13

you might be looking at using linked server to connect to this database from sql server.

1 Like
14

you mean from SQL Server use DataDirect? Not sure that would work. but then, if you should be able to use a linked server.

15

The data on the mainframe is connected to DataDirect so that other application like Access, Crystal etc can use the data.

I just thought that if little ol' MS Access could do that SQL surely can....but I don't think so....I am going to look into linked server...

Thanks!

16

remember that MS Access is a Desktop app. SQL Server does things differently.

1 Like