Pervasive SQL to MS Access table export

Is there a way to export an entire table from Pervasive SQL to MS Access without using SQL to SELECT and INSERT the data into Access? I'm looking for a way that's faster than using SQL to Select and the Insert the data.
PerfishentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PerfishentAuthor Commented:
I'm especially looking for a method that can be automated to import multiple tables or an entire Pervasive SQL database using VBA.
Bill BachPresident and Btrieve GuruCommented:
Do you REALLY want to *import* the data?  You can avoid the entire IMPORT process completely by using a Linked Table instead, where you access the data directly from the PSQL database.  This allows you to access the core data directly, and there is no data transfer required.

I know that you can import the data via the GUI by selecting the Import option from ODBC, then selecting all of the tables.  This is usually pretty quick, but still can be time consuming, and I'm not sure how easy it is to automate.  However, it is the best mechanism to avoid having to create your own tables and then handle the data separately.  Another possible idea would be to use a true ETL tool, which might offer slightly better performance especially if optimized for multi-threading).  Examples include Actian's Data Integrator, Informatica, Pentaho, and Jaspersoft (or just search for "ETL tool").
PerfishentAuthor Commented:
Yes, I need to Import the data for migration to another database. In some cases, I need the entire table. In others, I only need a few fields from a table. Ideally, I would like to be able to extract the entire table where applicable and then use SQL to extract individual fields where only part of the table is needed. I'd like to be able to do this through VBA since I need to extract data from multiple databases and multiple database years. With VBA, I can set up variables to plug in the database names and years.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Nick67Commented:
without using SQL to SELECT and INSERT the data into Access?
At the end of the day, something is going to wind up doing just that, because that's how relational database management systems exchange data.
If you have a Pervasive ODBC connection on the go
http://www.connectionstrings.com/pervasive/
You can write VBA that'll query a catalog object, create tables and suck data across the wire.
Or you can link tables, and then copy-and-paste, telling Access to save as a local table.
And then Access uses the same mechanisms to suck the data across the wire.

But it'll be SELECT and INSERT as the nuts and bolts.
You can use products that may optimize the process
If I recall rightly, you could get Pervasive to spit the tables out as csv files, but you are still then flanging those in and getting the data into Access tables.
I don't know if that would be faster or not.
I doubt it.
Bill BachPresident and Btrieve GuruCommented:
The linked database is your best option. Access is not the target, so importing once to Access and then exporting is senseless. Link Access to the PSQL database first.  Then, write your VBA code to pull out the fields you want and send it to the target database. You can iterate over the dataset in code if you want to help filter the data.

Again, what you are really doing here is an ETL process. A purpose-built ETL  tool is likely to be a lot easier and quicker in the end, if you can afford the learning curve.
PerfishentAuthor Commented:
I was hoping there was some kind of "Copy Table" or "Import Table" function in VBA that would work with PSQL and that would be faster than Selecting data and then writing it to a table.

The best approach I think then is to grab the table names with code like this:

Select Xf$Name from X$File where xf$flags <> 16

Loop through each table name and get its field names:

Select Xf$Name, X$Field.* from X$File, X$Field
WHERE Xf$Id = Xe$File and Xf$Name = 'SOME_TABLE' and Xe$DataType < 200
ORDER BY Xe$offset;

Within the field name loop, assemble a Create Table statement that uses the Pervasive table name and field names. Run the Create Table statement to create the table in Access.

Once the table is created, Select * from the same table in Pervasive, and run an Insert statement for each line in the result set that inserts the results into the Access table.

I was hoping for a shortcut. This will probably run pretty slowly. The database has hundreds of tables. The SQL code described above can be run from VBA and reused or tweaked as needed which is good. Are there any tricks I'm missing that might speed up the process?
Nick67Commented:
Once the table is created, Select * from the same table in Pervasive, and run an Insert statement for each line in the result set that inserts the results into the Access table.

If you can create linked tables, then I don't think you'll need more than one insert statement per table.
INSERT INTO NewAccessTable SELECT * FROM LinkedPervasiveTable;

And if you can create linked tables, you can use VBA to create copies which you then will either point at a new server, or make them local tables, the equivalent of copy-and-paste
It may be as simple as
DoCmd.SelectObject acTable, TheTableName,True 
DoCmd.RunCommand acCmdConvertLinkedTableToLocal

Open in new window

Bill BachPresident and Btrieve GuruCommented:
I agree with Nick67; you are doing it the hard way. With the InsertInto, you should not even need to create the tables in the target database yourself. It should be created automatically with the right data types and everything.
Nick67Commented:
Pervasive can be a persnickety thing to beat with a stick, though.
I've shoved an Access stick into a third-party Prevasive SQL database a time or two, but those were like Sage Simply Accounting's MySQL horror; there were a ton of 8 character tablenames and fieldnames and no relationships established at the db end in those cases.

Very, very hard to make head or tails of.
But if you can get linked tables going -- which is never as simple as it looks depending on how much control and knowledge you have about the Pervasive install -- it should be doable.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Ideally, I would like to be able to extract the entire table where applicable and then use SQL to extract individual fields where only part of the table is needed. I'd like to be able to do this through VBA ...
Then listen to Bill and Nick. Link the tables, and you have all the tables as TableDefs.

Then you can mix and match with VBA including calling specific and/or dynamic SQL to filter, modify, and export the tables.

/gustav
PatHartmanCommented:
I agree with the others.  You are making this way too complicated.  If you don't want to link all the tables at once, you can link to the catalog as Nick suggested, then link one table at a time, create a make table statement using Select *.  and run it.  You can probably do it with two dozen lines of code.

You have indicated that Access isn't the target but you haven't told us what is the target.  You could use Access as a slingshot.  Link to the Pervasive tables.  Link to the destination tables.  Run append queries that copy the data from source to target.

And finally, there are some tools out there that will do this for you.  I don't have a name handy but I used one recently to convert from FoxPro to SQL Server.  The tool had about a dozen source/target options and worked smoothly.  If this thread is still active tomorrow, I'll post a link.
PerfishentAuthor Commented:
I found a better way to do this. Linked tables are clunky, inflexible, and a pain to work with. Especially if you work with a financial database where new databases are created every year. For this project, I need to migrate 6 databases across 8 years for a total of 48 databases in all each with hundreds of tables. It would take forever to create linked tables, dump them to a CSV file, etc.

The following function is a flexible, reusable way of solving the issue. Using the RecordSet.Fields property, you don't have to know what the field names are, how many there are, etc. You can apply the same code to any query whether the query is selecting everything or only a few things from a table. Using dynamic SQL statements allows me to use a variable for the database name, so I can run the same code in a loop to cross all of the databases and database years.

I should probably give myself the points for this question, but I'll let it slide this time.

Public Function CreateFile(coNum As String, strSQL As String, tblName As String)
    Dim thePath As String
    thePath = CurrentProject.Path
    Set oFile = fso.CreateTextFile(thePath & "\Export Files\" & coNum & "-" & tblName & ".TXT")
   
    'SET UP THE CONNECTION TO THE DATABASE
    Set conn = psqlConn(CInt(coNum), CInt(yr))
    Set rs = psqlQuery(conn, CStr(strSQL))
    ctr = 1
    If Not rs.EOF Then
        Do While Not rs.EOF
       
            'WRITE EACH FIELD NAME TO THE TEXT FILE
            If ctr = 1 Then
                For Each itm In rs.Fields
                    oFile.Write Trim(itm.Name) & vbTab
                Next
            oFile.Write vbCrLf
            End If
            ctr = ctr + 1
           
            'WRITE EACH FIELD VALUE TO THE TEXT FILE
            For Each fld In rs.Fields
                oFile.Write cleanText(Nz(fld.Value, "")) & vbTab
            Next
            oFile.Write vbCrLf
            rs.MoveNext
           
        Loop
    End If
   
    oFile.Close
    Set oFile = Nothing
    rs.Close
    conn.Close

End Function
Bill BachPresident and Btrieve GuruCommented:
Wow.  If you wanted a simple way to extract the data to a CSV file, that would indeed have been substantially easier to answer.  Powershell can do the same thing in about 1/2 the number of lines.  I just wish you had been more specific about the desired target up front.
Nick67Commented:
If I recall rightly, you could get Pervasive to spit the tables out as csv files,
So instead, you are getting Access to do it, presumably to use VBA for some automation.  Ok.
I'm looking for a way that's faster than using SQL to Select and the Insert the data.
I doubted that building csv files with our friend the filesystemobject is going to be faster, but then Pervasive is a persnickety monster and maybe the ODBC driver really sucks.
without using SQL to SELECT and INSERT the data
Using dynamic SQL statements
This is different than an INSERT how?
Instead, you are pulling the data and doing an RBAR file-write.
If it is faster then knock yourself out!
to export an entire table
We assumed you wanted them in Access--not as csv files.
Because, while you can use a csv as a linked file in Access, they aren't really fast.

And hell, if you are going to go that route, then throwing in the DDE to create the table and a half a bazillion inserts is the same deal.

Is the Pervasive ODBC driver really that bad?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.