How do I populate a database with code??? Help!!!

[tcurtis]

I have written a code that will open database and .csv file and it compiles and no problems there. What I need to know is how to get it to write the records to the database instead of the debug window. I am trying to write the records from this file to the database through .MoveNexVB code....any suggestions? Thanks so much!
Here is what I have .... so far

Option Compare Database
Option Explicit

Dim dbsPractice1 As Database
Dim rstproject As Recordset
Dim wrkJet As Workspace
Dim BDS As Integer
Dim Filler As Long
Dim LoggedDate As Integer
Dim strDesignDescription As String
Dim strStatus As String
Dim strDeveloper As String
Dim strBusinessAnalyst As String
Dim Packet As Integer
Dim strDesignType As String
Dim strSystem As String
Dim ExpectedDate As Integer
Dim strDepartment As String

Sub Open_Records()
'create microsoft jet workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
'Opens Database
Set dbsPractice1 = wrkJet.OpenDatabase("C:\Practice1.mdb", True)
'Opens the project file to input it into the database
Open "C:\project.csv" For Input As #1
Do While Not EOF(1)
Input #1, BDS, Filler, LoggedDate, strDesignDescription, strStatus, strDeveloper, strBusinessAnalyst, Packet, _
strDesignType, strSystem, ExpectedDate, strDepartment
Debug.Print "Opening table-type recordset " & _
"where the source is a QueryDef object..."
Set rstproject = dbsPractice1.OpenRecordset( _
"BDS", dbOpenTable)

AddRecord rstproject, BDS, Filler, LoggedDate, strDesignDescription, strStatus, strDeveloper, strBusinessAnalyst, Packet, _
strDesignType, strSystem, ExpectedDate, strDepartment

Loop
Close #1
End Sub

Function AddRecord(rstproject As Recordset, BDS, Filler, LoggedDate, strDesignDescription, strStatus, strDeveloper, strBusinessAnalyst, Packet, _
strDesignType, strSystem, ExpectedDate, strDepartment)

' Adds a new record to a Recordset using the data passed
' by the calling procedure. The new record is then made
' the current record.
With rstproject
.AddNew
!BDS = BDS
!Filler = Filler
!LoggedDate = LoggedDate
!DesignDescription = strDesignDescription
!Status = strStatus
!Developer = strDeveloper
!BusinessAnalyst = strBusinessAnalyst
!Packet = Packet
!DesignType = strDesignType
!System = strSystem
!ExpectedDate = ExpectedDate
!Department = strDepartment
.Update
.MoveNext
.Bookmark = .LastModified
End With

End Function
Thanks so much for helping me!!
Tammy
Indiana

 

[CosmoKramer]

Tammy,

Why go through all that when you could use the TransferText method??

 

[tcurtis]

I wish I could do the transfer method but I am doing my externship and my boss wants to see what I am made of and wants me to do it with code.....Can you help me out?

 

[CosmoKramer]

I've never had the pleasure of taking the scenic route; it's always patch it up, get it working, and move to the next disaster....

Obviously you have used Access help for the AddNew method. I would take another look at it.

I would start by moving the OpenRecordset statement with the other Set statements. Then, change the parameter names that are the same as your field names to something else. See how much progress this makes, and hopefully someone with more experience with Recordset coding will join in....

 

[genomon]

Hi Tammy!
I don't have much time, but two things stand out: You have created a function that does not return a value - try using a sub and simply assign the data rather than passing in all those arguments. Also doubt if this is a problem, but could you just .Requery instead of .MoveNext / bookmark?
Sorry 'bout the rush - gotta fly...
-genomon

 

[Craig0201]

Tammy,

Just a point but a programmer should use the best method available to them. Think you'd gain far more kudos by being able to state the case that transfertext is the correct method to use rather than DAO or ADO.

Craig