Add data in table

[wwc_um]

I'm trying to add some data/records generated from some databases in a table named TblPprmloc in a .mdb file. How can I accomplished these task?


Thanks
Andy Wong

[Johnny101]

what sort of data? are yuo using VB to read from one database and insert that information into another database?

please expand on your question.

thanks,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com

[wwc_um]

My access file names passive.mdb has a table named PPRMLOC. How can I add 25 records in the table which has 3 fields. The code is below.


Set WsPassive = DBEngine.Workspaces(0)
Set DbPassive = WsPassive.CreateDatabase(txtTrgPath + "Passive.mdb", dbLangGeneral)
Set Tbl = DbPassive.CreateTableDef("PPRMLOC")
With Tbl
.Fields.Append .CreateField("NounCode", dbText, 8)
.Fields.Append .CreateField("FirstRec", dbInteger, 8)
.Fields.Append .CreateField("NumRecs", dbInteger, 8)
End With
DbPassive.TableDefs.Append Tbl
'Create a primary index on "NounCode" for unique record
Set Idx = Tbl.CreateIndex("IdxNounCode")
Idx.Primary = True
With Idx
.Fields.Append .CreateField("NounCode")
End With
Tbl.Indexes.Append Idx

inTfrt = 0
For i = 1 To 25
Set TblPsmain = DbPassive.CreateTableDef("Psmain" & aryFileNo(i))
TblPsmain.Connect = "FoxPro 2.5;" & "DATABASE=" & txtSrcPath
TblPsmain.SourceTableName = "psmain" + aryFileNo(i) + ".dbf"
DbPassive.TableDefs.Append TblPsmain

Set RsPsmain = DbPassive.OpenRecordset("Psmain" & aryFileNo(i))

TblPprmloc(i).strNoun = RsPsmain![nouncode]
TblPprmloc(i).lFrtRec = inTfrt
TblPprmloc(i).lNumRec = 0

Dim strFld_p7010 As String, strFld_p8001 As String, strFld_p8601 As String
strFld_p7010 = RsPsmain![p7010] & "" 'Get current values from the recordset a zero length string is appended to each
strFld_p8001 = RsPsmain![p8001] & "" 'variable to avoid the Invalid use of Null error if a field is null although current
strFld_p8601 = RsPsmain![p8601] & "" 'rules don't allow nulls, there may be legacy data that doesn't conform to existing rules

Do While Not RsPsmain.EOF
If Not IsNull(strFld_p7010) Or Not IsNull(strFld_p8001) Or Not IsNull(strFld_p8601) Then
TblPprmloc(i).lNumRec = TblPprmloc(i).lNumRec + 1
End If
RsPsmain.MoveNext
Loop
inTfrt = inTfrt + TblPprmloc(i).lNumRec

(HOW TO ADD RECORDS IN THE TABLE IN THIS LOOP)

Next i

[Johnny101]

You might be able to just build an insert statement and excute on the connection. since i'm not really sure what data yuo are trying to insert, this is just some psuedo code:

for i = 1 to 25

'all the same as you already have until the last while loop

Do While Not RsPsmain.EOF
If Not IsNull(strFld_p7010) Or Not IsNull(strFld_p8001) Or Not IsNull(strFld_p8601) then
TblPprmloc(i).lNumRec = TblPprmloc(i).lNumRec + 1
End If
RsPsmain.MoveNext
Loop

inTfrt = inTfrt + TblPprmloc(i).lNumRec

>(HOW to ADD RECORDS IN THE TABLE IN THIS LOOP)

sql = "INSERT INTO Psmain" & aryFileNo(i)) & " VALUES ("
sql = sql & "'" & NounCodeValue & "', "
sql = sql & FirstRecValue & ", "
sql = sql & NumRecsValue & ")"

'now execute this command
dbPassive.execute sql

next i




That's my suggestion. I hope it makes sense and/or helps.

Good luck,
John


John Pirkey
MCSD
www.ShallowWaterSystems.com