Link to home
Start Free TrialLog in
Avatar of holemania
holemania

asked on

ADP SQLBASE - Link to SQL Server

Hello Experts,

I'm trying to link a SQLBase database from ADP to my SQL Server so that I can use it with other databases in the SQL Server 2000.  However, I'm having issue trying to create the link.  

When linking, in the General tab, I put in the linked Server name, select the Gupta SQLBase OLE DB Provider, put in product name, data source (assume this is a datasource that is already connecting to this database), and provider string which similar to the following:  DSN=SQLBase Island;SERVER=SERVER1;DATABASE=PAYROLL;
UID=JOHN;PWD=XYZZY

However, no luck.  Anyone able to successfully done this or know how to go about getting this to work?
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

General tab - where do you see it? are you doing this from Access? how?
Avatar of holemania
holemania

ASKER

In enterprise manager for SQL Server 2000, I want to link ADP which use SQLBase.  I installed the Gupta driver and created the ODBC which I can connect to the ADP SQLBase database.  However, I would like it to link to SQL Server 2000 so that I can use it with the other databases.

To add in the link server, I went to SQL Server Enterprise Manager and drill down the node to Security-->Linked Servers.  From there, I right click to add a new  linked server.  Once I do that, that's where it allows me to put in the Data Source and Provider String.  I can't seem to connect to it when linking it to my SQL Server 2000, but I can access the data from Excel using the ODBC that I create.

ole db provider string - it's not ODBC; specify it in form

Provider=SQLBaseOLEDB;Data source=myServerAddress;Location=myDataBase;User Id=myUsername;Password=myPassword;

ADP has no place in all this; ADP is thin client providing GUI for SQL Server. It has no own objects, so there's no point in linking to it.

Further, I don't even understand how you linked ADP to SQLBase, since ADP works only with SQL Server and nothing else. If you somehow ODBC-linked the tables from SQLBase, it would be much easier to do with MDB.
I'm not sure what you meant when you said you don't understand how I link ADP to SQLBase.  The version of ADP we use, the database platform is SQLBase so there's no linking involve.  It uses the Gupta SQLBase.  From what our ADP tech told me, the newer ADP uses Oracle.  We have an older version that is SQLBase.

What I'm trying to do is link the ADP server to SQL Server 2000 so that I can update employee information within Kronos base off the information in ADP.  Another way of getting around this is dump the employee table from ADP into another table in SQL through ODBC, but it's not the most efficient.  I was hoping that I can link the ADP server instead.  I know it can be done, I'm just not sure what I'm missing to get it to work.  I have done it with excel file, oracle, and variouse access databases.  However, the SQLBase for ADP is a little funky and would not work correctly.  I can pull the data to an excel file as well through the ODBC and link that excel file to our SQL Server, but again it's not the most efficient way.
We are probably talking about different ADP's. This area, "Microsoft ADP", is dedicated to Microsoft Access Data Projects, which is one of the two types of Microsoft Access applications, another one being MDB. More details at http://office.microsoft.com/en-us/access/HP052731031033.aspx . I guess this is not what you had in mind?
No, I'm talking about ADP Payroll.  http://www.adp.com.  That could be why I wasn't clear on what you kept referring to lol.
Just noticed I click on the zone for Microsoft ADP.  That's wrong on my part.  I should've clarified it more.
ASKER CERTIFIED SOLUTION
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No luck and had to do it another way.  I was able to automate a dump from SQLBase to excel file, and then dump excel file to a temp table.  This seems to work.  Not what I was hoping to do, but it's a workaround.  Thanks for the help.
Was a misunderstanding on my part when I put it in the wrong zone.  Only response I got even though it didn't resolve my issue, deserve the credit for time in helping me out.
some notes for the purpose of KB:

1. to tes if particular OLEDB string is working, the easiest is to create a simple vbscript and see if it works. For example,

createobject("adodb.connection").connect( <adodb-string>).execute ("select 1")

2. in the properties of the linked server, make sure that "allow in-process" is checked

3. This link seems to provide the steps that lead to success:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107277

4. it's possible to configure Excel worksheet as linked server - see MS article 306397.