ODBC Connects

Hi there!

Apologies to you Whiz kids for my ignorance herewith but I am trying to link an SQL database table to another database table from which I want to import data.

I'm an "Access" database girl who has had to face the fact that my database must now become an adult and join the 'big league' and so I am currently "playing" in SQL as I try to come to grips with this new programme. In Access, ODBC links were so easy! I used to go to TABLES, select LINK TABLES, select my ODBC link, log into the other database and, viola!, the tables would be there. I'd select the one/s I wanted and could even limit the fields that came though.

How does one do this in SQL? I do have an SQL manual here but have no idea where to even start reading in that (and it's a BIG MANUAL!)

If anyone can give me some direction on how to do this, I would be most appreciative. Remember, I'm a baby in SQL so please keep it simple!

Thanks everyone!!

MariaI'm not much ahead of you and perhaps there's a better way but...

SELECT * FROM OPENROWSET( parameters )

-- OR --

SELECT * FROM table_name1 JOIN OPENROWSET( parameters)

would appear to fit the bill.

Trouble is I can't get the 'parameters' bits figured out! (see my earlier post 'openrowset parameters").

Also DTS (Data Transformatin Services) makes it a snap to import via ODBC etc but is tedious to maintain for anything more than quick fixes.|||Do you want to use SQL Server databsese tables in Access? If you do than you have two choices:

1) Use the "old fashion" ODBC Databases tehnique: first create an ODBC chanel to your SQLServer database (From ODBC Manager in Control panel, or in Administrative Tools if you are using Windows 2000. You can create either a file datasource or a machine datasource). Then you can use this chanel in you link table wizard.

2) Starting with Access 2000, you can have a different type of database: .adp - Microsoft Access Project. This type of "database" allows you to use Access as a front-end to your SQLServer database. This means that you have tables, queries, storeproc in SQL Server and Forms and Reports in Access (all in one single project - .adp) For these kind of project native driversfor SQLServer are used. With Access 2000 you can use SQLServer 7 database, and with Access 2002 (XP) you can use SQLServer 2000 databases.

.Adp are version dependent as you can see from above paragraph. With "link method" you can use any type of SQLServer database you want. The only thing you have to have is the correct version of ODBC drivers for SQLServer. You can download ODBC drivers from microsoft. They are found in a package called "mdac" (Microsoft data access components)

IONUT

PS
As an own opinion it's a very good ideea to migrate your databases to SqlServer, but you should also stop using Access even for a front end. It's very slow with large amounts of data, it has runtime libraries anly from XP version (as much as I know) and therefore is very expensive (you have to have a MS Office licence for each seat). It's true that it is easy to code and it has one of the best report designer Microsoft has ever build, but... that's all

Good luck!|||How would one link the tables of one (production DB) to another SQL DB?

Thanks!!|||Hi there!

Thanks for that. I am actually, trying to leave out Access altogether and link the SQL database (which will be my data warehouse) to the source database where staff completed their service statistics. To date, I am using Access to link to the souce database but this is becoming too large and, as you will appreciate, is slow.

The ODBC connnection, that I am already using for the Access link/import is the same for SQL (according to the software house who produce the source database) so I just need now to tell SQL to go and get the specified fields/tables from the source database and dump them into new tables in this new SQL database. That's the bit I'm having the problems with.

Cheers!

Maria

Originally posted by ionut calin
Do you want to use SQL Server databsese tables in Access? If you do than you have two choices:

1) Use the "old fashion" ODBC Databases tehnique: first create an ODBC chanel to your SQLServer database (From ODBC Manager in Control panel, or in Administrative Tools if you are using Windows 2000. You can create either a file datasource or a machine datasource). Then you can use this chanel in you link table wizard.

2) Starting with Access 2000, you can have a different type of database: .adp - Microsoft Access Project. This type of "database" allows you to use Access as a front-end to your SQLServer database. This means that you have tables, queries, storeproc in SQL Server and Forms and Reports in Access (all in one single project - .adp) For these kind of project native driversfor SQLServer are used. With Access 2000 you can use SQLServer 7 database, and with Access 2002 (XP) you can use SQLServer 2000 databases.

.Adp are version dependent as you can see from above paragraph. With "link method" you can use any type of SQLServer database you want. The only thing you have to have is the correct version of ODBC drivers for SQLServer. You can download ODBC drivers from microsoft. They are found in a package called "mdac" (Microsoft data access components)

IONUT

PS
As an own opinion it's a very good ideea to migrate your databases to SqlServer, but you should also stop using Access even for a front end. It's very slow with large amounts of data, it has runtime libraries anly from XP version (as much as I know) and therefore is very expensive (you have to have a MS Office licence for each seat). It's true that it is easy to code and it has one of the best report designer Microsoft has ever build, but... that's all

Good luck!|||LOL!! Glad to know there are others in the same situation as me!! I'll check out your posting re the parameters. The replies there may be exactly what I am after.

I've tried the DTS and it works, albiet slowly from Access but getting it to talk to the source software (Jade) is proving the problem. According to the software suppliers, it is the exact same ODBC link as used when importing in to Access but I can't get it work to date. I generally get an MMC.exe error and it 'packs a sad'.

I'll keep you posted on what I learn from here!

Cheers!

Maria

Originally posted by berniev
I'm not much ahead of you and perhaps there's a better way but...

SELECT * FROM OPENROWSET( parameters )

-- OR --

SELECT * FROM table_name1 JOIN OPENROWSET( parameters)

would appear to fit the bill.

Trouble is I can't get the 'parameters' bits figured out! (see my earlier post 'openrowset parameters").

Also DTS (Data Transformatin Services) makes it a snap to import via ODBC etc but is tedious to maintain for anything more than quick fixes.|||Also, have a look at "linked servers" under SQL Sercurity tab.

You still have to either input various parameters, but in my case this worked immediately USING A DSN.

And then
SELECT *
FROM OPENQUERY(linked_server_name, 'SELECT * FROM table_name')
-- works

To run DSN-less is still eluding me, but a reply to my post did lead me to save the DTS as VBscript which gave a heap of info. Problem is that the DTS seem to have its own way of doing things that is different to TSQL. Worse, it uses Microsoft Jet OLEDB 4.0, which SQLServer2000 Books on line says is for Access only! (I thought of you) I am using SQLServer7. Perhaps there's a difference. And all references to connectionproperties seem to refer to DTS programming only.