Hi:
I’m working on a problem with respect to exporting a BO report to MS ACCESS. The problem is that several of the fields (i.e. descriptions) contain tabs within them and so when I attempt to use tabs as a delimiter, it shifts data following the tabs to the next field, thus bumping the rest of the fields as well.
How can I export a report under these circumstances without this problem occuring?
Listserv Archives (BOB member since 2002-06-25)
In getting data into MS Access, it seems you are first saving data to file and then subsequently importing data into MS Access. There is a more direct method, ie Export to RDBMS. This method requires that you have a personal connection defined for your Access database using an ODBC Datasource. One issue I have run into is that if the end user wishes to do such on currently unexisting Access databases in the future, how does he/she create these BO connections? The only way I have found is by using the Free-hand SQL option. Exporting to RDBMS can directly export a cube’s data into Access by creating a table for the data.
Alternatively, you may consider another delimiter character although I do believe that you may be limited on the import delimiter characters available to you in MS Access and that they may appear in your data.
Donald May
Listserv Archives (BOB member since 2002-06-25)
You might also try the Data Export command. You can export your report into .dbf files then import them into Access.
Jeffrey Freed
HR Systems Manager
UNC Hospitals
Hedrick Building
211 Friday Center Dr.
Chapel Hill, NC 27614
“If it’s not fun, why do it?”
919-966-1425-- direct line
919-966-1415-- fax
jfreed.dhr1@mail.unch.unc.edu
Listserv Archives (BOB member since 2002-06-25)
Ok, I’m having a little problem trying to find the best solution to get a BO query into Access. In a related thread we discussed my problems with the"exporting to RDBMS option" here, but that thread took a turn as to why we want to take the data to Access in the first place. Now, I am exploring the defining a personal connection option, although hestitant to provide this as a solution, I want to test it on my end and I am having problems defining the personal connection. I am trying to do it through Designer / Tools / Connections, I go to add a personal connection, define as ODBC, give it a name and engine (MS Access 97 or 2000), but then I am a little confused about the password/username and data source. I define secured connections all the time but this personal connection has me confused. I never use Access but I would like to be able to successfully test this option. What would I use for a password and username and what is the datasource (should this be the Access file or DB name; if so, how does it initially get created). Maybe I am overlooking the obvious here, but I am stumped. 
jswoboda 
(BOB member since 2002-06-20)
Have you tried using the Business Objects Username and password ?
Anjan Roy (BOB member since 2002-07-10)
Generally for MS Access you can get by without a username and password. As far as how / when does the database get created, you can create an ODBC data source and create the database right from that screen. That will give you an “empty” database that you can connect to.
MS Access is - by default, I believe - not a secure database. Therefore no username or password is required.
Dave
Dave Rathbun (BOB member since 2002-06-06)
Thanks, I got into the ODBC Admin, played around, found the configure and create buttons (this is where the path and db file is created) and I think everything appears to be working now. fyi - I did not use a username or password. - thx
On a side note, do you think giving someone temporary access to Designer to create a personal connection is a viable solution for taking BO data into Access. We are trying to avoid enabling the the free-hand SQL options. Currently users are taking a file into Access and having to go through the Wizard (see related thread for details). I tried the save as .dbf option but ran into several problems due to naming conventions. Anybody out there using personal connections - how do you approach? Designer, free-hand SQL options, ect… I think I am correct in assuming I can temporarily allow Desinger privledges to create the personal connection and then disable. Once created, it will always be available on that PC - yes?
jswoboda (BOB member since 2002-06-20)
I am not sure, but the overhead is installing BO Designer on that PC in the first place (in addition to possible licensing issues). And exactly who would create that connection via Designer on that PC? You? The end-user 
?
Andreas 
(BOB member since 2002-06-20)
You should be able to create personal connections right from within reporter. No need to grant / install Designer.
Dave
Dave Rathbun (BOB member since 2002-06-06)
You can get to creating a personal connection by attempting to create a query using FREEHAND SQL. On the FREEHAND SQL page, there is a button to create a new connection. All connections created at this FREEHAND SQL page will be personal connections
I believe they only have this option if you haven’t disabled the CREATE AND EDIT CONNECTIONS from the Supervisor module.
-RM
digpen (BOB member since 2002-08-15)
Yea, I remembered about the possible licensing issues. We currently have free-hand SQL options disabled for all users. The powers at be do not want users to have this option. I suppose I can temporarily enable this for specific users and allow them to create the personal connections and then disable, thus avoiding any licensing issues.
jswoboda (BOB member since 2002-06-20)
I put together some instructions for our users to create personal/shared connections, so I thought I would make it available on BOB also. For those of you who are responsible for administering command restrictions, Connection and Free-Hand SQL options must be enabled for users to create personal or shared connections.
Creating Personal or Shared Connections through Business Objects
*** command restrictions must be enabled by the Business Objects Administrator (connection privilege and free-hand SQL) ***
· from the New Report Wizard, select generate a Standard Report
· from Data Access screen, select others, and Free-Hand SQL from the dropdown (this is the option that must be enabled, if you do not see this option you do not have access) then Finish
· at the Connection screen, select the button to create a new connection, select ODBC Driver and click OK
· at the Login tab provide a new connection name and database engine (MS Access 97 or 2000)
· select ODBC Admin button, on the DSN Tab select MS Access DB and configure button
· provide a data source name (ex. outbound call list) and description, in the Database section of this tab select Create (this is where you will associate a filename and path). Use the directory tree to define your path and Database name option to provide your file name (this will be the name of your Access .mdb file)
· Hit OK, you should get a confirmation message, and OK again. Your connection is now created, hit Cancel and you are now back at the ODBC driver window where you originally defined your Connection name and DB Engine. Select the Data source dropdown to select the data source you previously created. Hit the Test button to make sure your connection is responding.
· You then need to select the type (Personal only the user who creates the connection can use it or Shared any BO user on that PC can use)
· Hit Ok and this will take you back to the original Free-Hand SQL screen. Your newly defined connection should be in the connection box; if not, select from the drop down.
· You are now finished defining the connection and you can Cancel out.
· Now if you pull up your report in BO and select export Export to RDBMS will now be available and you can select your connection, hit OK and you will get a confirmation message.
· Your .mdb file will now be available to open in the appropriate folder specified in your connection
jswoboda (BOB member since 2002-06-20)
And all of this works for full client connections only, correct? A ZABO user could not use this approach, regardless of supervisor settings, right?
Dwayne Hoffpauir 
(BOB member since 2002-09-19)
Yea, I should of specified this was for FC users. Currently, we do not use ZABO so I can not provide any insight. Sorry.
jswoboda (BOB member since 2002-06-20)