using vba to write out contents of table

[red6000]

Hi, I have a very simple database as follows:

Table name = searchDB

Table has 3 fields: Subject, Keywords, Desc

What I would like to do is use VBA code to write the contents of the
database to a string. ie

Dim sdb as string

For each record in Table
sdb = value of subject field + ","
sdb = value of Keywords field + ","
sdb = value of Desc field + "/////"
Next record

I know this should be dead easy, but I cant seem to find anything on google
to point me in the right direct.

Can anyone help with a link that will get me on the right road

thanks.

 

[Daniel]

What you want to do is loop through the recordset

try something like

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sdb as string
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("searchDB")
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
sdb = rst![Subject] & "," & rst![Keywords] & "," & rst![Desc] &
"/////"
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[red6000]

brilliant that works great. I've got it working perfectly now. My code is
below. My next question is how can I improve the code so that it uses
notepad instead of Word as the code slows right down whilst it tries to open
and write to word?
*****************************
The bit that I'm trying to improve by using notepad is:

Set objWord = CreateObject("word.application")
Set objWord = GetObject(, "word.application")
objWord.Documents.Add
objWord.Visible = True
objWord.Selection.TypeText Text:=searchDB
objWord.ChangeFileOpenDirectory "J:\PCS\"
objWord.ActiveDocument.SaveAs fileName:="searchDB.js",
FileFormat:=wdFormatText
objWord.ActiveDocument.Close wdSaveChanges = False
*****************************
The full code is

Sub buildJavaScript()

Dim rs As Recordset
Dim db As Database
Dim strSQL As String
Dim objWord As Object
Dim searchDB As String
Dim recordPosn As Integer
Dim rec As String
Dim str As String
recordPosn = 0
str = Chr(34)
searchDB = "searchDB = new Array();" + Chr(13)
Set db = CurrentDb
strSQL = "Select * from [searchdb];"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
rec = recordPosn
searchDB = searchDB + "searchDB[" + rec + "] = new searchOption(" + str
searchDB = searchDB + rs!Subject + str + ", " + str + rs!Keywords + str
searchDB = searchDB + ", " + str + rs!Description + str + ", " + str +
rs!url + str + ");"
searchDB = searchDB + Chr(13)
rs.MoveNext
recordPosn = recordPosn + 1
Loop
Set objWord = CreateObject("word.application")
Set objWord = GetObject(, "word.application")
objWord.Documents.Add
objWord.Visible = True
objWord.Selection.TypeText Text:=searchDB
objWord.ChangeFileOpenDirectory "J:\PCS\"
objWord.ActiveDocument.SaveAs fileName:="searchDB.js",
FileFormat:=wdFormatText
objWord.ActiveDocument.Close wdSaveChanges = False

End Sub

What you want to do is loop through the recordset

try something like

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sdb as string
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("searchDB")
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
sdb = rst![Subject] & "," & rst![Keywords] & "," & rst![Desc] &
"/////"
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Hope this helps,

Daniel P

Hi, I have a very simple database as follows:

Table name = searchDB

Table has 3 fields: Subject, Keywords, Desc

What I would like to do is use VBA code to write the contents of the
database to a string. ie

Dim sdb as string

For each record in Table
sdb = value of subject field + ","
sdb = value of Keywords field + ","
sdb = value of Desc field + "/////"
Next record

I know this should be dead easy, but I cant seem to find anything on
google
to point me in the right direct.

Can anyone help with a link that will get me on the right road

thanks.

 

[Daniel]

Take a look at
http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000027#WriteExtFile

The Write to an External File is what you want. You don't need to open
Notepad you can simply do it through automation.
--
Hope this helps,

Daniel P

brilliant that works great. I've got it working perfectly now. My code is
below. My next question is how can I improve the code so that it uses
notepad instead of Word as the code slows right down whilst it tries to open
and write to word?
*****************************
The bit that I'm trying to improve by using notepad is:

Set objWord = CreateObject("word.application")
Set objWord = GetObject(, "word.application")
objWord.Documents.Add
objWord.Visible = True
objWord.Selection.TypeText Text:=searchDB
objWord.ChangeFileOpenDirectory "J:\PCS\"
objWord.ActiveDocument.SaveAs fileName:="searchDB.js",
FileFormat:=wdFormatText
objWord.ActiveDocument.Close wdSaveChanges = False
*****************************
The full code is

Sub buildJavaScript()

Dim rs As Recordset
Dim db As Database
Dim strSQL As String
Dim objWord As Object
Dim searchDB As String
Dim recordPosn As Integer
Dim rec As String
Dim str As String
recordPosn = 0
str = Chr(34)
searchDB = "searchDB = new Array();" + Chr(13)
Set db = CurrentDb
strSQL = "Select * from [searchdb];"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
rec = recordPosn
searchDB = searchDB + "searchDB[" + rec + "] = new searchOption(" + str
searchDB = searchDB + rs!Subject + str + ", " + str + rs!Keywords + str
searchDB = searchDB + ", " + str + rs!Description + str + ", " + str +
rs!url + str + ");"
searchDB = searchDB + Chr(13)
rs.MoveNext
recordPosn = recordPosn + 1
Loop
Set objWord = CreateObject("word.application")
Set objWord = GetObject(, "word.application")
objWord.Documents.Add
objWord.Visible = True
objWord.Selection.TypeText Text:=searchDB
objWord.ChangeFileOpenDirectory "J:\PCS\"
objWord.ActiveDocument.SaveAs fileName:="searchDB.js",
FileFormat:=wdFormatText
objWord.ActiveDocument.Close wdSaveChanges = False

End Sub

What you want to do is loop through the recordset

try something like

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sdb as string
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("searchDB")
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
sdb = rst![Subject] & "," & rst![Keywords] & "," & rst![Desc] &
"/////"
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Hope this helps,

Daniel P

Hi, I have a very simple database as follows:

Table name = searchDB

Table has 3 fields: Subject, Keywords, Desc

What I would like to do is use VBA code to write the contents of the
database to a string. ie

Dim sdb as string

For each record in Table
sdb = value of subject field + ","
sdb = value of Keywords field + ","
sdb = value of Desc field + "/////"
Next record

I know this should be dead easy, but I cant seem to find anything on
google
to point me in the right direct.

Can anyone help with a link that will get me on the right road

thanks.