MS Access Reading from ODBC Connection

[fadilrexhepi]

Hi,



I am in need of consultation for MS Access reading data from ODBC connection. I have SQL Server that has all the data for the project financials etc.
I need a database that will read only certain data from the tables, for example, I don't need to import all 500,000 lines from SQL through ODBC connection, I just want to bring certain data for a list of projects whichever are opened and load only that data in MS Access so the group then can add additional details for that project in a shared MS Access.

Please advise if this is possible to do

Right now, all I can do is connect to that database through ODBC and brings all the data which I don't need all as it increases the size of the database, but just a criteria to specify which data to bring, if that's possible to do

Best regards,
Fadil

[lfpm062010]

I am not sure I understand what you are asking.

To answer your question that not bring all the data back through ODBC is as simple to write a make table query and select what record that you do want.

I am not sure why you need to bring any data back from SQL Server through ODBC. The purpose of SQL Server is to host your data (not to mention SQL Server security is much better than Access).

[fadilrexhepi]

Hi,

Thank you for your answer. I really don't know much about SQL Server and ODBC connections since I may be a rookie in this subject and I might confuse you with my questions.
If there is a possibility to do make table query and the query to get the data for the projects listed or whatever variable listed, then that solves my issue for sure.
Will try this option and let you know

Regards,
Fadil

[fadilrexhepi]

Hi,

Is it possible to create in MSACCESS make table query that will use ODBC connection and bring specific data for a variable or number of variables

Thank you
Fadil

[lfpm062010]

The short answer is yes. There are couple of ways that I can think of. One is using Access link table. The other is using Access Pass Through Query.

I don't know what you have done so far. This is what I would have done.
1) In Access, create a linked table to SQL SERVER Database table.
2) In Access, create a make table query use the link table. You have to provide a local Access table name.
3) Select the field that you want and excute the make table query. The table will be created with the SQL SERVER data.

[fadilrexhepi]

Hi,

I really want to thank you for your time and readiness to respond and provide help.
Your answer gets me a step closer to what I want to do.

So far, I have just linked the tables through ODBC and when I click on the table, it shows details and it is working fast and the data is not copied localy in the msaccess.
However, now I have 8 tables for 8 different company codes and they all have the same structure. So, now, if I make table query to create local msaccess table and combine all these SQL Server data tables into one, will that data still reside on the SQL Server or will it be copied locally on that tables because it is lots of data?

[orange]

This sounds like a less than optimal structure

I have 8 tables for 8 different company codes and they all have the same structure.

You say

I really don't know much about SQL Server and ODBC connections since I may be a rookie in this subject

You really should research database design. You seem to have a lot of technology and some general ideas
of how it "fits together", but I can assure you there is more to database than technology. And getting tables and relationships designed to meet your requirements is fundamental.
lfpm062010 has given good advice.

Good luck with your project.

[lfpm062010]

However, now I have 8 tables for 8 different company codes and they all have the same structure.

Please see Orange's response.

So, now, if I make table query to create local msaccess table and combine all these SQL Server data tables into one, will that data still reside on the SQL Server or will it be copied locally on that tables because it is lots of data?

Make table query will recreate the table everytime. You will have to use append query to append to the access local table after the first one. SQL Server data and Local Access table data will be seperate. The data will not be lost. You should be able to verify that. After make table and append query, check to see if the SQL Server data still there. If it does not, that mean you did something wrong and hope you have a backup of the SQL Server database to recover from.

Good luck to you and Hope this will get you going.