getting data into an ADO-recordset

[Helger]

I want to get data from an SPSS file into an ADO recordset.
What's the most elegant way to do this?

If noone happens to have experience with SPSS - is there a standard way to find out how to do stuff like this?


thanks in advance

Helger

P.S.: Since someone asked before: SPSS file means file created with the statistics program SPSS - file ending '.sav'

[jbart]

I don't know if this is the most elegant way to do it, but here goes. First you will need to set a Project Reference to the latest version of the ActiveX Data Object Library. Then in your code dimension and set a connection object and a recordset object. The code below is an example using an Access database.

'Connecting to an Access Database using ADO
Option Explicit
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the database connection and recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
            "Persist Security Info=False"
    rs.Open "Select * from Customers", cnn, adOpenStatic, adLockOptimistic
End Sub

Private Sub Form_Unload(Cancel As Integer)
    rs.Close
    cnn.Close
    Set cnn = Nothing
    Set rs = Nothing
End Sub

[Helger]

hmm thanks that certainly is a good way of connecting to an Access database. My problem is rather to read in data from files created by the above mentioned statistics program.

only one table is possible in SPSS, it does not have restrictions on number of fields (not that i know of that is - i know for sure there can be more than 2000) or records.
in that it resembles an excel table without restrictions.

maybe i need a driver for that? does anyone know?

it's possible to save data in SPSS in .mdb format. so it should be possible to get the data directly, right? my problem being that the person using my app wont want to change the data format himself all the time.

regards

Helger

[InvisibleDuncan]

I would expect that you'll need a driver to do this, much as you need an Oracle driver (or Microsoft's Oracle driver) to connect to an Oracle database. Because you say that it can only have one table, though, it's possible that it is saved as some type of text file - CSV or whatever. Take a copy of the .sav file and try opening it in a few applications - Wordpad, Excel, even Access etc and see if you have any success. If you can open it, then use the appropriate driver for whatever file type it turns out to be.

If you can't get it open, ask the manufacturer if they have any ODBC drivers for it.

Happy hunting.

[Helger]

thanks duncan, it will be the manufacturer then - couldn't open with access or excel

Helger