Importing Import/Export Specs

[dapotter]

I have an Access database that I use to generate reports on data in a text-based company database. I extract data from the company database into a .csv file, which I link to in Access. Several other people have copies of my database, each with some of their own queries/reports defined.

I have recently changed the structure of the .csv file (added/removed/rearranged various fields). I have updated my import spec to account for the new layout. I would like to write a VB module for the other users that will update the import specs in their copies of the database. I understand that they will have to import the module that will import their import specs, but hopefully they can re-use the module the next time I mess with the .csv file layout.

It looks like I would use the TransferDatabase method to import or link database objects. But I don't see anything in the interface that supports the Import/Export Specs checkbox included with the Import Objects options. Is it possible to script the import of the Import/Export Specs?

Thanks,
Don

 

[traingamer]

See thread705-818506 and/or seach the forum for 'import spec'.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[JerryKlmns]

dapotter

Just to add that the only way to delete a spec from the MSysIMEX tables is only through code like

yourSpecID=5
CurrentProject.Connection.Execute "DELETE M.* FROM MSysIMEXColumns As M WHERE M.SpecID=" & yourSpecID
CurrentProject.Connection.Execute "DELETE M.* FROM MSysIMEXSpecs As M WHERE M.SpecID=" & yourSpecID

Export the specs to an excel, modify them and import back from excel. The import of the new spec is like any other import to a table!
The bad thing is that those tables arent any more hidden or recognised as system tables. Hide them yourshelf by hand.

 

[JerryKlmns]

Ooops! Submitted too fast.

The ini file is my favorite option, though

 

[dapotter]

Jerry,

I tried using the schema.ini file (as was recommended in the thread Greg pointed me to). I used the following code that I found on a Microsoft website.

Function LinkSchema()
   Dim db As DATABASE, tbl As TableDef
   Set db = CurrentDb()
   Set tbl = db.CreateTableDef("Linked Text")

   tbl.Connect = "Text;DATABASE=c:\my documents;TABLE=contacts.txt"
   tbl.SourceTableName = "contacts.txt"
   db.TableDefs.Append tbl
   db.TableDefs.Refresh
End Function

This code fails when attempting to append the table to the tabledefs collections. It tells me that the database or the object is read-only. But upon inspection, the Updatable property for both db and tbl is True. I gave up on that method and am now working on importing the MSysIMEXSpecs and MSysIMEXColumns tables.

You can probably tell that writing VBA code is not my primary job. I'm an Ada programmer. On the rare occaisions that I get to work with VBA, I feel like I'm starting from scatch. If there's something I'm missing concerning the use of the schema.ini file, I'd love to hear about it.

Thanks,
Don

 

[JerryKlmns]

Following is my ADOX approach

Sub ImportSchema(Pinakas As String, Cur_Cnn As ADODB.Connection)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
    Set cat.ActiveConnection = Cur_Cnn
    Set tbl.ParentCatalog = cat

    With tbl
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link Provider String") = "Text"
        .Properties("Jet OLEDB:Link Datasource") = Data_Folder
        .Properties("Jet OLEDB:Remote Table Name") = Pinakas & ".txt"
        .Name = "N_" & Pinakas
    End With
    cat.Tables.Append tbl
    cat.Tables.Refresh

    Cur_Cnn.Execute "Delete From " & Pinakas & ";", , 129
    Cur_Cnn.Execute "Insert Into " & Pinakas & " Select N_" & Pinakas & ".* From N_" & Pinakas & ";", , 129

    cat.Tables.Delete "N_" & Pinakas
    Set tbl = Nothing
    Set cat = Nothing

End Sub

WHERE
Pinakas, is the table name to import to + the file name to import from. Imports all the file as is into the table
Cur_Cnn, is the connection in use since I import in multiple mdbs

You need a reference to your version of Microsoft ADO Ext. 2.x for DDL and Security
Keep in mind that the Schema.ini file should reside in the same folder where the txt file to import is and the section name has the exact name of the file to import.
Tip use the Name ... As ... VB statemnt to copy and rename the file to import!

Good Luck