ADO RecordSet with Schema INI to control data types
Hello:
As I have been reading, In order to have ADO recordsets not read in the wrong data type, I need to create a schema file.
My frustration is, I have no idea how this file is referenced in my connection string / recordset. The articles re not clear on this.
I would like to be able to use this format for all files:
The FileName is MasterSchema.ini
Code:
[E7068911-Export.csv]
Format=CSVDelimited
Col1 = Level Text Width 20
Col2 = PN Text Width 15
Col3 = Desc Text Width 150
Col4 = Qty Double 3
Col5 = Rev Text Width 2
Col6 = RawMatl Double 3
Col7 = MatlQty Double 3
Col8 = MatlUnits Text Width 10
And my connection string code is here:
Code:
Dim cn As ADODB.Connection = New ADODB.Connection
Dim sql As String = "SELECT * FROM [" & CSVFILENAME & "]" ' ORDER BY 1" ' Cannot add this because of 1.1.10 issue
Dim rs As ADODB.Recordset = New ADODB.Recordset
cn.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & CSVFULLFOLDERPATH & "; Extended Properties = ""text;HDR=Yes;FMT=CSVDelimited""; ")
rs.Open(sql, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
Do I need a filename to match every filename in the ini file? Where is this referenced in my code so that it gets used.
Re: ADO RecordSet with Schema INI to control data types
Is this a VB .Net project or a VB6 project? Because in .Net we normally use ADO .Net to work with data. ADODB was used in VB6. It's possible to use ADODB in .Net but not the best practice. If it's a VB6 project then you've posted in the wrong forum. You need to post in the VB6 forum.
Re: ADO RecordSet with Schema INI to control data types
To answer your question rename you Schema file from "MasterSchema.ini" to just "Schema.ini" and make sure it's in the same dir as your csv/txt file.
You should certainly be looking into ADO.Net or other texty methods of connecting to your data as Wes has mentioned.