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.

<!--[if gte mso 9]> Normal 0 false false false oNotPromoteQF /> EN-US X-NONE X-NONE ontGrowAutofit /> ontVertAlignCellWithSp /> ontBreakConstrainedForcedTables /> ontVertAlignInTxbx /> MicrosoftInternetExplorer4 <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> <!--[if gte mso 10]> Keeping in mind that one of the ultimate goals of LS9 is to have a single set of login credentials, securing the EMPLOYEE table so that only the right records are accessible is of paramount importance. This is critical for people who are application users and will have a search box in portal and thus will be able to access HR11 (required by Self Service).

The EMPLOYEE table can be secured for the ESS role to only allow access if COMPANY/EMPLOYEE in the table = COMPANY/EMPLOYEE in the employee’s identity. The EMPLOYEE table can be secured for the MSS role by using the IsSupervisorOf function which gives access to only the direct reports of a manager. So, when both are in force, the less restrictive MSS role wins out and all is good.

Enter the Org Chart link. The Org Chart allows one to navigate thru the organization’s structure (via HS10) and see who reports to whom. At any time you can click on a person’s name and get a profile of that person. And where does that profile come from? The EMPLOYEE table! So, if I click on the CEO’s name, since I am neither the CEO nor he/she is my direct report, the applet just spins and returns nothing. Sleuthing into the lase logs shows that “NO_ACCESS” was returned as a result of the EMPLOYEE table’s security rule from the MSS role (as it should!). Unfortunately the applet just spins, it doesn’t return any kind of “Security Violation” message or anything like that.

So, is the answer to get Org Chart to work to open up access to EMPLOYEE in the ESS/MSS roles? I don’t think so since an application user would then have access to the full employee table from HR11 drill/selects.

 This is my quandary. 

Any ideas?

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.