Access Lawson tables in MS Access

Yes, you can install the ODBC drivers for your Lawson database and connect (I suggest read only) to your tables. I've done this in the past to create reports (and prior to getting ProcessFlow) trigger notifications.

Which does mean that you are bypassing Lawson (LAUA or LS9) security.

You can also connect via OLE DB which is slower but does enforce some security.

and you can have the DBA set up some table security to keep people in their area (HR, GL, AP)

You can also connect via OLE DB which is slower but does enforce some security.

Thank you for your help.

As I am not a db administrator (I am a end user in finance), I don't have an actual database userid (only the Lawson WEB password). I would very much like to load the data directly to MS Access than via Excel Sheets (which is what we are currently doing). I assume that for any ODBC/OLE-DB connections you need to have the proper userid/password for the underlying database?

Thanks P.

For ODBC you would have to install the drivers to connect to the database and have a "read only" login into the data tables. Your database admin would have to set that up for you.

I would not suggest loading data via Access. When you load from excel, you are loading an actual Lawson form and following all business and system logic. If you loaded directly to the tables, there is about a 99% chance that you would miss a relationship or update to another table. and corrupt your entire system.

If you were to elaborate on why you would want Access, maybe someone on here can point you in the most efficient path.

Yeah, I would't go that route either for the reasons mentioned, for what it's worth.  Lawson sure wouldn't support that endeavor.

There would only be a concern about corruption if they were wanting to load data into Lawson. I believe they are only asking about extracting data.

If you only have an OLEDB login, one option is creating linked tables in Access that are pointing to your existing excel files.

Unless someone has discovered something I'm not aware of, you can't use OLEDB with Access.

As mentioned above by some, I strongly suggest NOT using M/S-Access to perform data loads / updates to your Lawson tables. We've allowed our 'super users' (read only) access to the Lawson tables via ODBC using M/S-Access and Crystal for 10 years now. And in some cases I've developed queries/macros that all the user has to do is type in the parms and run. Takes some of the workload off our MIS staff when quick ad-hoc requests come down the pike.

I am unable to connect our test (LSF & 9.0) database.  I can connect to test with Crystal Reports but not Excel (addins is not an issue) or Access.  When you log in using either Microsoft product it just hangs and never connects nor throws an error. 

We ran a trace and we are not seeing any errors -

msqry32         3b8-898 ENTER SQLAllocEnv
  HENV *              0C0F7F28

msqry32         3b8-898 EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
  HENV *              0x0C0F7F28 ( 0x008a1788)

msqry32         3b8-898 ENTER SQLDataSourcesW
  HENV                008A1788
  UWORD                       31
  WCHAR *             0x008A1830
  SWORD                       34
  SWORD *             0x0012F1CC
  WCHAR *             0x00000000
  SWORD                        0
  SWORD *             0x0012F192

msqry32         3b8-898 EXIT  SQLDataSourcesW  with return code 0 (SQL_SUCCESS)
  HENV                008A1788
  UWORD                       31
  WCHAR *             0x008A1830 [      18] "MS Access"
  SWORD                       34
  SWORD *             0x0012F1CC (18)
  WCHAR *             0x00000000
  SWORD                        0
  SWORD *             0x0012F192 (31)

msqry32         3b8-898 ENTER SQLDataSourcesW
  HENV                008A1788
  UWORD                        1
  WCHAR *             0x008A1830
  SWORD                       34
  SWORD *             0x0012F1CC
  WCHAR *             0x00000000
  SWORD                        0
  SWORD *             0x0012F192

msqry32         3b8-898 EXIT  SQLDataSourcesW  with return code 0 (SQL_SUCCESS)

Any words of wisdom to resolve our issue would be appreciated.

Thanks Sheila

We too have been allowing power users to connect to the Lawson tables via MS Access/ODBC. It does take some heat off of us analysts/report writers but out DBA's are of the oppinion that these users who are running adhoc queries and really don't know much about creating proper queries are causing issues with the environment.
Does anyone have any insight into this?
Thx.
Eric Maher
Seattle Children's

Does anyone know how to connect Access to Lawson using OLEDB connection?  How and what driver do you use?

Unless someone has discovered something I'm not aware of, you can't use OLEDB with Access.

You have to use the ODBC driver for your native database provider, i.e. SQL Server,Oracle, DB2.

Eric,

If you are allowing users to connect to the Lawson database via ODBC, and these users are not familiar with querying a relational database (but even more specifically, the Lawson table relationships), then it is absolutely possible (and I would even say likely) that these adhoc queries are creating performance issues with your Lawson environment.

Thanks,
Chris

I used to use a tool called Brio (now Oracle Interactive Reporting). We had built a view that connected GL tables or AP tables or HR tables. Each user could only see their area. The views and the tool helped keep the queries proper and we never experienced system slowdown from the masses.

I was able to drop the whole system once or twice by bypassing some limits.

I worked closely enough with IT to avoid writing bad queries in Access. I would strongly suggest building the base queries for users and not let them loose on the tables.

Okay just to recap what has already been stated:

1) Connecting to Lawson via OLEDB using MS Access = No
2) Connecting to Lawson via ODBC using MS Access = Yes
3) Connecting to Lawson via OLEDB using MS Access by utilizing linked tables = Yes. You can do this by a) connecting to Lawson via OLEDB using Excel and then b) connecting to the Excel file(s) from MS Access using linked tables

Allowing adhoc users to only access pre-developed database views is indeed an excellent way to approach this.

 Allowing adhoc users to only access pre-developed database views is indeed an excellent way to approach this.

I have an entirely separate Oracle Read-Only permissions to get to the Lawson tables via Crystal Reports, MS Access, WinSql, etc. I've been using Access with Lawson Tables for years. Price of admission is you have to study the tables inorder to make the proper joins. Start queries with requests for very small data volumes until you get all you are looking for before letting it rip for large chunks of data. An optional but valuable resource is using the Ctrl + Shift + O functionalty in Portal to select not only the correct field but the one in the correct table as well. Beauty of Access is I can create my own Lawson Code Description Tables for example APAUDIT table has 90+ codes with no corresponding table that I know of, to give them any meaning. Creating a Table with 2 fields, the Code & the Description from the Lawson Documentation, then join with APAUIDT 1-1 with the Audit Code and bingo a report the boss can understand. Access is powerful, however tamed with read only permissions to Lawson via a mirror Oracle Database on the table level, View permissions on restricted Table fields, and the Access steriod brakes (Ctrl + Break) yields tremendous benefits with very little risk and costs the shareholders $0.00

I guess my question then becomes for letter a).  How do I add the OLEDB driver to my list of available choices in Excel, so I can proceed with option 3.