11 Replies Latest reply: May 15, 2014 10:04 AM by drobert _ RSS

    Is it possible to export to SQL database?

    drobert _

      I was searching through help and this forum and found nothing, which may be the answer to my question, but I will ask just to be sure... is it possible to export directly to an SQL database?

       

      If this is not possible, I think that I would be able to setup an Access database using links to the SQL tables needed and fool Monarch in thinking that the tables are actually Access (I have not yet tried this as this is my plan "B").

        • Is it possible to export to SQL database?
          drobert _

          In the meantime, I tried to create a link in Access to the SQL table, and that worked fine, but when I exported it to the table in Access, it deleted the link and created an Access table instead (whether I used the /exptableopt:append or not), which defeated what I was trying to accomplish.

            • Is it possible to export to SQL database?
              joey

              I've used a SQL and DB2 tables as input, but never output.

               

              I grabbed this from the help file in Monarch.  Since I don't see ODBC or other databases listed I'm guessing you may not be able to.

               

              Export file formats and

              capacities (per table)

              Lotus WK3 (8,192 records)

              Excel 2.1 (16,384 records)

              Excel 3.0 (16,384 records)

              Excel 4.0 (16,384 records)

              Excel 5.0 (16,384 records)

              Excel 8.0 (65,536 records)

              Excel 2007 XLSX (1,000,000 records)

              Excel 2007 XLSM (1,000,000 records)

              dBASE III (255 fields - 3850 characters, 9,999,999 records)

              dBASE IV (255 fields – 3850 characters, 9,999,999 records)

              dBASE 5.0 (255 fields – 3850 characters, 9,999,999 records)

              Paradox 3.X (255 fields - 4000 characters, 9,999,999 records)

              Paradox 4.X (255 fields - 4000 characters, 9,999,999 records)

              Paradox 5.X (255 fields - 4000 characters, 9,999,999 records)

              Paradox 7.X (255 fields - 4000 characters, 9,999,999 records)

              Access 1.0 (255 fields - 2000 characters, 9,999,999 records)

              Access 1.1 (255 fields - 2000 characters, 9,999,999 records)

              Access 2.0 (255 fields - 2000 characters, 9,999,999 records)

              Access 3.0 (255 fields - 2000 characters, 9,999,999 records)

              Access 4.0 (255 fields - 2000 characters, 9,999,999 records)

              ANSI and ASCII Text files (9,999,999 records)

              ANSI and ASCII Delimited text (9,999,999 records)

              HTML (65536 default cell limit)

              PDF

                • Is it possible to export to SQL database?
                  drobert _

                  I've used a SQL and DB2 tables as input, but never output.

                  /quote

                  Yeah, so have I.

                   

                  I grabbed this from the help file in Monarch. Since I don't see ODBC or other databases listed I'm guessing you may not be able to.

                  ...

                  /quote

                  I saw this as well and this what made me think that it was not possible.:(

                   

                  I really thought that fooling Monarch in thinking the table was from Access (which it actually was a link to the SQL table) that it would simply pump the data to it, but is seems to verify that it's a link and replace it with a table... unless I am not using the /exptableopt: option correctly because even if I specify /exptableopt:append it recreates the table, which kind-of contradicts the actual intent:confused:.

                   

                  I would imagine that someone must have come across this before and maybe (hopefully) found a way to do this.

                   

                  This is important for us as our organisation has enforced a new policy that ALL data must be store in a centralized SQL server (to improve security and to also simply replication for DR site) as opposed to Access and DBF (yes, we still have some old applications that used DBF:o) tables all over the place.

                    • Is it possible to export to SQL database?
                      Data Kruncher

                      It's time for you to consider upgrading to Monarch's big brother, [URL="http://www.datawatch.com/_products/monarch_data_pump_pro_tech.php"]Monarch Data Pump Pro[/URL].

                       

                      Data Pump can export to OLE DB/ODBC Data Sources (e.g. MS SQL Server, Oracle, DB2) directly, where, as you've discovered, Monarch cannot.

                       

                      Fortunately your Monarch skills will have provided a good foundation for you and your company should you decide to proceed with Data Pump as it is built upon Monarch technology.

                        • Is it possible to export to SQL database?
                          drobert _

                          It's time for you to consider upgrading to Monarch's big brother, [URL="http://www.datawatch.com/_products/monarch_data_pump_pro_tech.php"]Monarch Data Pump Pro[/URL].[/quote]

                           

                          Oh, I see. That's too bad we did not know this before we upgraded to 10 Pro. I'll have an even harder time convincing my manager that we now need Data Pump;). Oh well, such is life...

                           

                          Merci Kruncher, pour ta clarification.

                            • Is it possible to export to SQL database?
                              Data Kruncher

                              :D:D:D De rien, mon ami.

                               

                              Good luck with your project. Remember to use the right tool for the job, and data Pump seems to be a good solution for many that I've met.

                               

                              Having said that, Mr. Perkins can speak to Data Pump's ability more so than I.

                                • Is it possible to export to SQL database?
                                  Scott Eshleman

                                  :D:D:D De rien, mon ami.

                                   

                                  Good luck with your project. Remember to use [URL="http://********************/databases/building-database-applications-with-monarch"]the right tool for the job[/URL], and data Pump seems to be a good solution for many that I've met.[/B]

                                   

                                  Having said that, Mr. Perkins can speak to Data Pump's ability more so than I.[/quote]

                                   

                                  While I cannot agree more wholeheartedly with Kruncher, I have not been able to effectively convince my Senior Mgmt to spend for the upgrade to Monarch Data Pump (MDP) either.  I've likely shot myself in the foot, having found other workarounds in the past...especially in this economy and the price difference for MDP. 

                                   

                                  Anyhow, I haven't tried this, but piggybacking on drobert's idea of using Access to 'fool' SQL:

                                  keep your already-created Access mdb referenced by your central SQL

                                  export your table or summary from Monarch to another Access mdb or Excel file[/U]

                                  create the link in the 1st mdb to the table in the 2nd mdb or XLS

                                  the table in the 2nd mdb (or XLS) should be able to be overwritten without affecting the link in the 1st mdb or its reference by SQL

                                  /LISTAgain, I haven't tried this technique to feed SQL specifically as you would like to do, but I've used it successfully with exports used as circular references within the same Monarch project. 

                                   

                                  In fact, Kruncher's website has illustrated these uses of Monarch as it's own datasource for subsequent Monarch projects and as database back-end tool that has seeded many other ideas:

                                  [URL="http://********************/databases/building-database-applications-with-monarch"]Importing Your Exports[/URL]

                                  [URL="http://********************/sources/video-monarchs-external-lookups"]Monarch's external lookups post & video[/URL]

                                  [URL="http://********************/calcfield/comparing-monarchs-internal-and-external-lookups"]comparingmonarchs internal and external lookups[/URL]

                                   

                                  I hope Kruncher doesn't mind a little praise for his website. 

                                  He's helped me & so many others in this forum and with his work.

                                    • Is it possible to export to SQL database?
                                      Data Kruncher

                                      IIRC - now that I think about it - RalphB has posted about the technique that you've described. I'll see if I can find it. In the meantime: Ralph, any further comments?

                                       

                                      Thanks for the plugs and, once again, the compliments Scott. As always, I appreciate your support and assistance in spreading the message. . Something about working with data...

                                        • Is it possible to export to SQL database?
                                          RalphB _

                                          IIRC - now that I think about it - RalphB has posted about the technique that you've described. I'll see if I can find it. In the meantime: Ralph, any further comments?

                                           

                                          Thanks for the plugs and, once again, the compliments Scott. As always, I appreciate your support and assistance in spreading the message. . Something about working with data... ;)[/QUOTE]

                                           

                                          Sandy, I tried to search for my post on this but I haven't been able to find it.

                                           

                                          My problem was I was unable to export into a password protected db directly from Monarch.  My solution was to export out to a temp database and from there I was able to run an append query to export into the protected database. 

                                           

                                          I haven't had a need to export out to a SQL db from Access, but if you can do that via a SQL statement, I see no reason why this wouldn't work.

                                           

                                          This is a sample of the VBA code I run out of Excel to import into a password db.

                                           

                                          stExpDB = "DATABASE=G:\db\Final.mdb"

                                          stPWord = "PWD=Password"

                                          stCurrentDB = "G:\Accounting\Temp.mdb"

                                          Set AccessApp = CreateObject("Access.Application")

                                          AccessApp.OpenCurrentDatabase stCurrentDB

                                          stTable= "Sample"

                                           

                                          stSQL = "INSERT INTO [;" & stExpDB & ";" & stPWord & "]." & stTable & " SELECT * FROM " & stTable & ";"

                                           

                                          AccessApp.DoCmd.RunSQL stSQL

                                           

                                          AccessApp.CloseCurrentDatabase

                                          AccessApp.Quit

                                          Set AccessApp = Nothing

                                            • Is it possible to export to SQL database?
                                              joey

                                              An idea I had from reading this:

                                               

                                              -Export from Monarch to an Access table (Monarch_Input)

                                              -In the Access database, have the SQL tables linked (SQL_Output)

                                              -Have a macro that uses queries to write your records from Monarch_Input to SQL_Output

                                              - You can call the macro from a batch script, or however else you're planning on automating this.

                                               

                                              As someone who uses Data Pump a great deal (my company bought it before I was hired, so I didn't go through convincing my boss), it's a wonderful thing.  I know it's pricey, but it's easy to justify with a few key reports.

                                                • Is it possible to export to SQL database?
                                                  drobert _

                                                  An idea I had from reading this:

                                                   

                                                  -Export from Monarch to an Access table (Monarch_Input)

                                                  -In the Access database, have the SQL tables linked (SQL_Output)

                                                  -Have a macro that uses queries to write your records from Monarch_Input to SQL_Output

                                                  - You can call the macro from a batch script, or however else you're planning on automating this.[/quote]

                                                  Thanks Joey. Yes, that was my plan B but I was sure that I would be able to fool Monarch in thinking that it was an Access table it was updating.

                                                   

                                                  Note: I should correct my findings, although the problem still exists. I indicated that it overwrote the link I created pointing the the SQL table, but that is not the case as I forgot to specify "/expfileopt:add" so it recreated the database and hence had to create the table. When I use the "/expfileopt:add" option, and try to append my data the the "pseudo-table", Monarch simply quits without exporting anything.[/B]

                                                   

                                                  As someone who uses Data Pump a great deal (my company bought it before I was hired, so I didn't go through convincing my boss), it's a wonderful thing. I know it's pricey, but it's easy to justify with a few key reports.[/quote]

                                                  I haven't even bothered yet to ask him as I am fairly confident what the answer will be:mad:.

                                                   

                                                   

                                                  I haven't had a need to export out to a SQL db from Access, but if you can do that via a SQL statement, I see no reason why this wouldn't work.

                                                  /quote

                                                  Thanks, RalphB. This is similar to Joey's suggestion and I was planing on trying this out soon. My alternative was to have my DBA do the same thing but in SQL Server and pointing in the opposite direction.

                                                   

                                                   

                                                   

                                                   

                                                   

                                                   

                                                  Anyhow, I haven't tried this, but piggybacking on drobert's idea of using Access to 'fool' SQL:

                                                  keep your already-created Access mdb referenced by your central SQL

                                                  export your table or summary from Monarch to another Access mdb or Excel file[/U]

                                                  create the link in the 1st mdb to the table in the 2nd mdb or XLS

                                                  the table in the 2nd mdb (or XLS) should be able to be overwritten without affecting the link in the 1st mdb or its reference by SQL

                                                  /LIST[/quote]Thanks Scott for you suggestion. If I understand you correctly, you are having the SQL table point to the Access table, but it was actually the other way around that I was trying to accomplish this. As indicated in response to RalphB suggestion, it is my plan C to have it point in the opposite direction and have my DBA create a script to copy the data from the linked Access table to the true SQL table. The only thing that I don't like about this is that there can be some timing issues, where the extract may not have been done or in progress and the SQL server fires with incomplete or no data.

                                                   

                                                  I would rather fire the VBA script myself from Access to get the data from the table to the link pointing to the SQL table, but I am certainly keeping your suggestion in mind.

                                                   

                                                   

                                                  Merci encore Sandy pour tout ton aide, c'est toujours bien apprécié:D.