Access Query to Text File

[1druid1]

Hi All

I have had a request from my company to be able to output a text file formatted as an ini file from a database query.

Its a strange request but I do see what they are looking for, basically this ini file will drive a display screen that was designed a few years ago but we no longer have support for to get developed, but it runs off an ini file. Instead of manualy having to edit the ini file, they want the database to control the screen.

My knowledge of Access 2007 is very limited, I created the database but its very basic, several tables, forms, queries ect.

Unfortuantly this request is above my means so looking for some help.

Now the data for the screen will only ever have a maximum of 10 rows so I have created a query based on the top 10 results.

Row--Field1--Field2--Field3--Field4--Field5
1-----Data1--Data2--Data3--Data4-Data5
......
10---DataA--DataB--DataC--DataE-DataF

The ini file is structured as.

[DisplaySetup]
Row1Field1=
Row1Field2=
Row1Field3=
Row1Field4=
Row1Field5=
.....
Row10Field1=
Row10Field2=
Row10Field3=
Row10Field4=
Row10Field5=

So I tried to create a query based on the follwoing

Field1
Expr1:"[DisplaySetup]"
Field2
Expr2: "Row1Field1=" & Field1
Field3
Expr3: "Row1Field2=" & Field2
Field4
Expr4: "Row1Field3=" & Field3
Field5
Expr5: "Row1Field3=" & Field4
Field6
Expr6: "Row1Field3=" & Field5

Using this I can export a CSV txt file that looks simialr to the following

"[DisplaySetup]","Row1Field1=Data1","Row1Field2=Data2","Row1Field3=Data3","Row1Field4=Data4","Row1Field5=Data5"
.....
"[DisplaySetup]","Row1Field1=DataA","Row1Field2=DataB","Row1Field3=DataC","Row1Field4=DataE","Row1Field5=DataF"

From the above you can see that my file is a CSV so with the inverted commas and commas the formating is completly wrong and the fact that my Row1Field1 the row is always static with 1 instead of each row having the proper row number as I could figure out how to get row numbers.

Not sure if what my company wants is possible through a little coding, but if it is any help is appreciated.

Reagrds

Druid

 

[WayneRyan]

Druid,

Dim row As Integer
Dim clm As Integer
Dim rst As DAO.Recordset

Open "C:\SomeFile.INI" For Output As #1

Set rst = CurrentDb.OpenRecordSet("Select Top 10 Field1, Field2, Field3, Field4, Field5 " & _
                                  "From YourTable " & _
                                  "Order By Field1")
For Row = 1 To 10
   For clm = 1 To 5
     Print #1, "Row" & Cstr(Row) & "Field" & CStr(Clm) & "=" & rst.Fields(clm)
     Next clm
   Next Row
Close #1

hth,
Wayne

 

[1druid1]

Hi Wayne

Thanks for the reply, but I have no idea what to do with this, do I put it in the coding of a button, create a module and paste it into a module ect?

Regards

Druid

 

[1druid1]

Hi Wayne

I figured out the code, great help and changed it slighty and works perfectly, here is my updated code.

Private Sub Command0_Click()
Dim row As Integer
Dim clm As Integer
Dim rst As DAO.Recordset
Open "C:\MyIni.ini" For Output As #1
Set rst = CurrentDb.OpenRecordset("Select Field1, Field2, Field3, Field4, Field5 " & _
                                  "From MyTable " & _
                                  "Order By Field1")
                                  Print #1, "[BasicSetup]"
 
For row = 0 To rst.RecordCount - 1
   For clm = 0 To rst.Fields.Count - 1
     Print #1, "Row" & CStr(row + 1) & "Field" & CStr(clm + 1) & "=" & rst.Fields(clm)
        Next clm
        rst.MoveNext
Next row
rst.Close
Close #1
End Sub

I will put this code on a timer and it will upadate the ini file every 5 seconds or so.

Great help.

Thanks

Druid