MS Access and SQL or Maybe a CSV - Best Wey to Integrate?

[cmcfarland]

Hello!

I have a client that manages their law firm using an application built in and around MS Access, and I need to get some data from a copy/print usage system into a table in that system.

I can link to the SQL directly, if that's best, and my system also generates a CSV file every night that I could use, too, if that were to be better.

There's the existing table in MS Access to account for, so if I were to connect to the SQL data it would have to be available along with the MS Access table and be able to be queried as a single entity.

Not sure if that's possible as all.



Maybe in import of the data would be better. Not opposed to that. If I do am import process I'll need for it to happen automatically and with no human intervention.

I could import from my SQL, or use the CSV files if that would be better.

So, thank you very much for taking a look at my issue and I can't wait to hear what you've got to say.

Thanks!

[pbaldy]

My first option would be to link the table. To query them as a single entity, you can use a UNION query to pull them together.

[cmcfarland]

Yes, I like the way that sounds!

Sounds like this would be query driven, which is really great news, too.

To break it down to my kindergarten math level of MS Access understanding, it sounds like when someone goes in to pull data from the MS Access table the data that they see would be what shows as a result of the UNION based query that I would write into the system.

If that's a fair understanding about the process I am really excited to find out how I'd set things up to have that UNION query based system set up.

I'd be referencing a table in MS SQL and in MS Access, so that would be new to me, but query writing in general is not.

Is it practical to lay out the basics for putting that in place?

Thanks!

[pbaldy]

I'm not clear on what you need help with, but Leigh has a lot of info here regarding using SQL Server with Access, including connection methods:

http://www.utteraccess.com/forum/Beg...-t1732935.html

[cmcfarland]

Thanks for the reference, I'll check it out now.

Here's what I'm looking for:

If a Form is used to access the data in the Access system, I'd like for it to return the results of the Union query.

Also, if someone were to query the table directly it would be cool if that were the same there.

What do you think?

I think they may use a Form to get to that data so maybe the UNION query would be part of that form?

Am I on the right track?

[pbaldy]

You could certainly base the form on a UNION query, or let users query it (normally that would be through an interface you create). Warning, a UNION query is read-only. You'll have to jump through some hoops if you want the users to be able to edit the data.

[cmcfarland]

OK, that's perfect!

Right now I'm hitting the books to begin writing my query, and what I can't find anyone talking about is how to write the query pulling from both the Access table and the SQL table. Seems like every UNION article I find is talking about using tables from a single database category.

I'm also a little concerned about the way a UNION needs the columns to match up, but that worries me less. Once I'm able to construct queries that use both the Access and SQL tables I feel confident that I can write something that works.

What do you think?

[pbaldy]

If the tables are linked it doesn't matter where the source is. The FROM clause would simply use the name of the table, or the name you give it when you link it if different.

[cmcfarland]

I see.

So, what I need to figure out is how to link them!

Digging in to that now!

[pbaldy]

I think Leigh's FAQ covered that, but post back if you get stuck. You can create an ODBC DSN (user, system or file) and link with that, or use DSN less code.