I need VB code to populate my Database in access...HELP!!!

[tcurtis]

I cannot figure out how to populate my database with a .csv file! This is for my externship and I am desparate...help!! The name of my database is Projectcsv.mdb and the name of my .csv file is project.csv! I need good old VB code that will open read and write this .csv file to my newly created databse.

Here is the code I have and thanks to any help I get!
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
Write #1, 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


Can someone tell me what I am missing here???
Thanks again,
Tammy, Indiana

 

[rewdee]

Hi Tammy

I think if you use the DoCmd TransferText method like below You would be better off:

DoCmd.TransferText acImportDelim, , BDS, project.csv, False

As for your code. You have a few problems. One only set the recordset once before the loop. Two, I think you need to call ReadLn (oops, that might be the Delphi syntax) as opposed to write#.

I hope this helps,
Rewdee

 

[tcurtis]

I tried the do command and the boss said nope....he wants VB code....I am currently serving my externship and he wants to see my coding. : (

 

[rewdee]

Hi Tammy

Obviously your boss is not a programmer because the best code is no code that's why programs like Access our so popular.

When is this due? I don't have the time to write and test it but I'm sure I could do it if you give me a day.

Rewdee

 

[tcurtis]

LOL....I always thought the same thing that efficiancy was best. I have a couple days to do this. I am doing my externship and you are very right ...he told me that since I am having such a problem that I don't have what it takes to be a developer. That hurt my feelings. I was always taught what you just said and I was also taught that the way I did it was best(macros) but I can't make him listen to me. I think he is just mad because I completed the other projects with in minutes and now he has me stum,ped....thanks! If you can help me I would greatly appreciate it!