sharing of data

[mission2java_78]

We have several apps in ms access where we have say a customers table being shared. We recently have developed several apps in other dbs such as sql server. The customer data is needed in SQL server...but is it really that good of an idea to link tables from access to sql server? Can you do that? Or should it be the other way where I need to port all the current data from MS access and then link my access customers table from the sql table.

The reason for this is we are going to get our customers data from SAP now. I will need to parse the data from a CSV file and store it in the table. Should I just store it in my SQL Server customers table and then create a link to our access apps? Will the code in access be ok if I do this?

Thanks,
Jon

 

[mission2java_78]

Anyone on this?

What about daily updates..I need to make sure updates are made to the databases at last nightly...or first thing every morning...how can I accomplish this. Meaning I can easily parse my data into the tables..but how should i trigger this AUTOMATICALLY each morning. Should I do this using the first user who logs in in the morning? I was scared of doing it like this because it might cause the system to be slow in the beginning and the user might just log out.

Jon

 

[RichMorrison]

Jon,

You have an Access front end MDB.

You can link a mix of back end tables from native-mode Access and SQL Server.

But..................
if you try to join Access tables to SQL Server tables in a query, the query will suffer a huge performance hit.

If you do need to join tables in a query, then it is best to import your SQL Server back end data into a native Access table.

RichM

 

[mission2java_78]

Jon,

You have an Access front end MDB.

You can link a mix of back end tables from native-mode Access and SQL Server.

But..................
if you try to join Access tables to SQL Server tables in a query, the query will suffer a huge performance hit.

If you do need to join tables in a query, then it is best to import your SQL Server back end data into a native Access table.

RichM

Like a make table query?

I mean I dont want any work as to having someone go in there and port the data...I would want that data ported automatically. We are spending way too much time with administration on the data.

Jon

 

[RichMorrison]

Jon,

This is how I report on Oracle mainframe data using Access as the front end.

First, a mainframe DBA creates a stored procedure to select data. This procedure accepts parameters, usually a from date and to date.

I write a function to call the stored procedure. The function uses ODBC and ADO. The data is returned in an ADO recordset and the function copies the data to a local Access work table.

After that, I treat the copied Oracle data like any Access table.

Reports and forms use data from the work table and join the work table to other local tables for grouping by department, region, area, etc. The reports and forms use queries that contain parameters which allow the user to filter data based on values from combo boxes or text boxes.

That's a general method which works well for me.

RichM

 

[mission2java_78]

Ok so there is a sproc in oracle and u call that sproc from your access front end?

How does one call a sproc from access...i forgot that this was a possibility

Jon

 

[RichMorrison]

"sproc" ?

That's a new one ! I like it.

You create an ADO connection and specify the provider, user id, password, etc.

Then you create an ADO command. In the command text you put something like this
<<
"{call APSNOA_pkg.get_apsnoasdi (?,?,?, {resultset 1000000," _
& "Eff_Date, Eff_Year, Eff_PP, Proc_Date, Proc_Year, Proc_PP, EmpID," _
& "Tech_Name, Adjust_Type, Adjust_Code, Remarks})}"
>>

This is a copy/paste from an Ac2K module. APSNOA_pkg is a package name on the mainframe. get_apsnoasdi is procedure in the package.

The mainframe Oracle stuff is done by another DBA so I don't really understand a lot about it. It does work.

HTH,
RichM