Linking to more than one data source

[OnTheFly]

I am fairly new to .adp creation. I am working in Access 2000. The situation is that I have most of my data tables in SQL Server 2000. The problem is that I also need to access and use several tables from DBase (.db). Is there a way to link to more than one datasource in an Access Project in Access 2000?

Hope this helps.

OnTheFly

 

[cmmrfrds]

There is only 1 connection to SQL Server. You can structure a query and connect to almost any data source through ADO. If you explain how the DBase data relates to the SQL Server data, I can suggest some alternatives.

 

[OnTheFly]

The application calculates salesperson commissions based on the sales to an account. The Sales information and all the account information is contained in a DBase database (that is DOS based). What the program does is pull the appropriate information from the DBASE tables so it can calculate the appropriate commissions for a particular salesperson based on the account information.

In a regular .mdb I would have a link to the SQL data and to the DBASE tables. This works OK but I like the security I can get from an .adp since this is payroll information.

I thought about just importing the data from the DBASE tables into SQL but am having trouble finding out how and also, it seems to be kind of slow at importing the data versus linking to it.

Any suggestions would be greatly appreciated.

Thanks,

OnTheFly

 

[cmmrfrds]

If you want to import the DBase tables to sql server then do that on the server side through DTS. You DBA can set that up for you.

Another option is to treat the DBase tables as linked servers on the sql server side. This would allow you to treat then as sql server tables in your app. The draw back is that the DBA must maintain and they may not want to.

Another option is to use the OPENROWSET function in sql server. This function allows you to combine data from different sources in the query. What is needed is to build the query in the app and send the query that includes the OPENROWSET function to sql server and sql server will combine the data for the query.

Another option, if the DBase tables are small is to store the data in an ADO recordset and use it from the recordset in the app. The down side is it cannot be combined with the sql server data in a query.

These are the options I can think of now.

 

[OnTheFly]

Thanks for the suggestions. I thought of importing the data through DTS and that will work only if I can run it on command. I have tried creating a Stored procedure to execute DTSrun but I have not been able to get it to work successfully. I need them to update on the fly because if changes are made to the database the Access portion needs to be updated.

I will look into the other options.

I appreciate your help.

Thanks,

OnTheFly