Hi guyz, just trying out this google feature so if i post if in the
wrong area i appologize. now to my question. BTW i'm new to access
programming, i've done a little vb6.0 and vb.net but access vba is a
little confusing for me since i have never used it plus i have lost
touch with vb coding since last 8 monhts.
anywayz, my problem is i'm trying to export a table that's modified by
me which is taken from a file at a prompt, now i have to export the
same file in the same format it came in Fixed and Delimited formats. I
can do this easily using the DoCmd.TransferText acExportDelim, "ORDER
Export Specification", "TBL0219", "C:\Test.txt" . All i do is change
the acExport to Fixed.
The file being imported has fixed width properties. It has 2 types of
records M(main set of record i.e Cust name, ph, email etc) and S(if the
operator upsold items it adds these records w/ corresponding field).
Once it is modified it now has just main record all M's. Now exporting
this format is easy but due to constraints i have to have the new
database output to the same format it used to be in which mean i have
to check now which one of the fields now has additional upsale items
i.e added QTY and PartNo.
Any ideas on how i can work this?
Example
1. Raw input file looks like
MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc
S 1 3009
2. After my modification it looks like
MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc 1 3009
3. Output needs to look like raw data again
Any help will be apprecaited
thanks Neil 10 14566
Neil wrote: Hi guyz, just trying out this google feature so if i post if in the wrong area i appologize. now to my question. BTW i'm new to access programming, i've done a little vb6.0 and vb.net but access vba is a little confusing for me since i have never used it plus i have lost touch with vb coding since last 8 monhts.
anywayz, my problem is i'm trying to export a table that's modified by me which is taken from a file at a prompt, now i have to export the same file in the same format it came in Fixed and Delimited formats. I can do this easily using the DoCmd.TransferText acExportDelim, "ORDER Export Specification", "TBL0219", "C:\Test.txt" . All i do is change the acExport to Fixed.
The file being imported has fixed width properties. It has 2 types of records M(main set of record i.e Cust name, ph, email etc) and S(if the operator upsold items it adds these records w/ corresponding field). Once it is modified it now has just main record all M's. Now exporting this format is easy but due to constraints i have to have the new database output to the same format it used to be in which mean i have to check now which one of the fields now has additional upsale items i.e added QTY and PartNo.
Any ideas on how i can work this?
Example 1. Raw input file looks like
MTea 20050218 James Connor 1 1 1 3005 2003 4003 etc MBag 20050219 Joanne Smith 1 1 1 3005 2003 4003 etc S 1 3009
2. After my modification it looks like
MTea 20050218 James Connor 1 1 1 3005 2003 4003 etc MBag 20050219 Joanne Smith 1 1 1 3005 2003 4003 etc 1 3009
3. Output needs to look like raw data again
Why don't you use the same import/export specification to export the
query/table data to a text file?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
> Why don't you use the same import/export specification to export the query/table data to a text file?
-- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA)
Well it would be easier if my import were the same specification as
export. See when i import it does not have the extra fields that add
once i manipulate the file. That's the whole point i'm importing it so
i can read the file and make it the way i want it. I cannot add those
fields in advance coz unfortunately the data file comes in such way and
the reason we export the same way is coz its gonna go to a diff place
where it may be read the same way or not. That system is setup to
accept the same way our systems are. So unless we change all the
systems which could be expensive or time consuming, i have to import it
the way it is change it keep that Table and export it the way it came
in.
This is a sample code my cousin helped me with, can someone help me
fill the rest!
Function TextExport_Fixed()
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenRecordset(tblname)
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If Left(rs.Fields(i).Name, 3) = "QTY" Then
Else
End If
strPad = Space(rs.Fields(i).FieldSize - Len(rs(i)))
strFix = strFix & rs(i) & strPad
Next
strFix = strFix & vbCrLf
rs.MoveNext
Loop
rs.Close
Call WriteToFile
End Function
Can anyone help me finish the rest of this code my cousin help me put
together
Function TextExport_Fixed(Content As String, Filepath As String)
'DoCmd.TransferText acExportFixed, "LIVEOPS ORDER FIXED Export
Specification", "OVEN0219", "C:\Test2.txt"
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenRecordset(tblname)
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If Left(rs.Fields(i).Name, 3) = "QTY" Then
Content =
Else
End If
strPad = Space(rs.Fields(i).FieldSize - Len(rs(i)))
strFix = strFix & rs(i) & strPad
Next
strFix = strFix & vbCrLf
rs.MoveNext
Loop
rs.Close
Call WriteToFile
End Function
Public Function WriteToFile(Content As String, Filepath As String,
Optional Append As Boolean = False) _
As Boolean
'If Append = true, then content will be appended to existing file
contents
'else existing file is overwritten
'Returns: True if Successful, false otherwise
Dim iFile As Integer
iFile = FreeFile
If Append Then
Open Filepath For Append As #iFile
Else
Open Filepath For Output As #iFile
End If
Print #iFile, Content
SaveToFile = True
ErrorHandler:
Close #iFile
End Function
Neil wrote: Hi guyz, just trying out this google feature so if i post if in the wrong area i appologize. now to my question. BTW i'm new to access programming, i've done a little vb6.0 and vb.net but access vba is a little confusing for me since i have never used it plus i have lost touch with vb coding since last 8 monhts.
anywayz, my problem is i'm trying to export a table that's modified by me which is taken from a file at a prompt, now i have to export the same file in the same format it came in Fixed and Delimited formats. I can do this easily using the DoCmd.TransferText acExportDelim, "ORDER Export Specification", "TBL0219", "C:\Test.txt" . All i do is change the acExport to Fixed.
The file being imported has fixed width properties. It has 2 types of records M(main set of record i.e Cust name, ph, email etc) and S(if the operator upsold items it adds these records w/ corresponding field). Once it is modified it now has just main record all M's. Now exporting this format is easy but due to constraints i have to have the new database output to the same format it used to be in which mean i have to check now which one of the fields now has additional upsale items i.e added QTY and PartNo.
Any ideas on how i can work this?
Example 1. Raw input file looks like
MTea 20050218 James Connor 1 1 1 3005 2003 4003 etc MBag 20050219 Joanne Smith 1 1 1 3005 2003 4003 etc S 1 3009
2. After my modification it looks like
MTea 20050218 James Connor 1 1 1 3005 2003 4003 etc MBag 20050219 Joanne Smith 1 1 1 3005 2003 4003 etc 1 3009
3. Output needs to look like raw data again
Why don't you use the same import/export specification to export the
query/table data to a text file?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
> Why don't you use the same import/export specification to export the query/table data to a text file?
-- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA)
Well it would be easier if my import were the same specification as
export. See when i import it does not have the extra fields that add
once i manipulate the file. That's the whole point i'm importing it so
i can read the file and make it the way i want it. I cannot add those
fields in advance coz unfortunately the data file comes in such way and
the reason we export the same way is coz its gonna go to a diff place
where it may be read the same way or not. That system is setup to
accept the same way our systems are. So unless we change all the
systems which could be expensive or time consuming, i have to import it
the way it is change it keep that Table and export it the way it came
in.
This is a sample code my cousin helped me with, can someone help me
fill the rest!
Function TextExport_Fixed()
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenRecordset(tblname)
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If Left(rs.Fields(i).Name, 3) = "QTY" Then
Else
End If
strPad = Space(rs.Fields(i).FieldSize - Len(rs(i)))
strFix = strFix & rs(i) & strPad
Next
strFix = strFix & vbCrLf
rs.MoveNext
Loop
rs.Close
Call WriteToFile
End Function
Can anyone help me finish the rest of this code my cousin help me put
together
Function TextExport_Fixed(Content As String, Filepath As String)
'DoCmd.TransferText acExportFixed, "LIVEOPS ORDER FIXED Export
Specification", "OVEN0219", "C:\Test2.txt"
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenRecordset(tblname)
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If Left(rs.Fields(i).Name, 3) = "QTY" Then
Content =
Else
End If
strPad = Space(rs.Fields(i).FieldSize - Len(rs(i)))
strFix = strFix & rs(i) & strPad
Next
strFix = strFix & vbCrLf
rs.MoveNext
Loop
rs.Close
Call WriteToFile
End Function
Public Function WriteToFile(Content As String, Filepath As String,
Optional Append As Boolean = False) _
As Boolean
'If Append = true, then content will be appended to existing file
contents
'else existing file is overwritten
'Returns: True if Successful, false otherwise
Dim iFile As Integer
iFile = FreeFile
If Append Then
Open Filepath For Append As #iFile
Else
Open Filepath For Output As #iFile
End If
Print #iFile, Content
SaveToFile = True
ErrorHandler:
Close #iFile
End Function
Can anyon tell me why i am getting a Run-Time Error 3259: Invalid Field
Data Type for the following line:
strPad = Space(rs.Fields(i).FieldSize) - Len(rs(i))
All i'm trying to do is find the padding i need for the fixed width,
and add it to the string when i output the string to my file.
strPad = Space(rs.Fields(i).FieldSize) - Len(rs(i))
Parens are wrong.
strPad = Space(rs.fields(i).fieldsize - Len(rs(i))
This awful thing worked...
Public Sub WriteToFixedWidth(ByVal strTable As String)
Dim db As DAO.database
Dim tdf As DAO.tabledef
Dim rs As DAO.Recordset
Dim intCounter As Integer
Dim strTemp As String
Set db = CurrentDb
Set tdf = db.TableDefs(strTable)
Set rs = db.OpenRecordset(strTable, dbOpenForwardOnly)
Do Until rs.EOF
For intCounter = 0 To rs.Fields.Count - 1
strTemp = strTemp & rs.Fields(intCounter).Value &
Space(rs.Fields(intCounter).Size - Len(rs.Fields(intCounter).Value)) &
"|"
If intCounter = rs.Fields.Count - 1 Then
'--NOTE: Instead of writing to the screen here,
'--------use Write to add to your open file.
Debug.Print strTemp
strTemp = ""
End If
Next intCounter
'Debug.Print strTemp
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Matt |
last post by:
I have an ASP page that calls ASP routines that I created that execute
a database query and return the results to a recordset. I then iterate...
|
by: Neil |
last post by:
Hi guyz, just trying out this google feature so if i post if in the
wrong area i appologize. now to my question. BTW i'm new to access
programming,...
|
by: excyauseme |
last post by:
Hi guys! Do you know what is the best way to export a text file, this
one is a log file that is already comma delimited thru a module run by
my...
|
by: Daniel Wetzler |
last post by:
Dear MSSQL- experts,
I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the...
|
by: Fabry |
last post by:
Hi All,
I'm new of this group and I do not know if this is the correct group
for my question.
I have a DLL with its export library (.lib) wrote...
|
by: NEWSGROUPS |
last post by:
I have data in a table in an Access 2000 database that needs to be exported
to a formatted text file. For instance, the first field is an account...
|
by: bhodgins |
last post by:
Hi,
I am new on here, and had a newbie question that I am stumped with.
I am not new to access, but am new to VB.
I am trying to export BLOBs from...
|
by: Edward Reid |
last post by:
OK, I've been watching my DB grow and have known for a long time I
would have to move the back end, but of course I waited until I
started getting...
|
by: iheartvba |
last post by:
Hi I am using Access 2007 and am trying to export a query to a fixed length text file. I tried using the following code to export the text file:
...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was...
|
by: Matthew3360 |
last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function.
Here is my code.
...
|
by: Matthew3360 |
last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
|
by: Matthew3360 |
last post by:
Hi,
I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
|
by: Oralloy |
last post by:
Hello Folks,
I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA.
My problem (spelled failure) is with the...
|
by: Carina712 |
last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
|
by: Rahul1995seven |
last post by:
Introduction:
In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...
| |