DB problems??

[fenris]

I wrote some ado code to create an access DB along with all the necessary tables and fields. I am now having a problem putting the information into separate tables and having them linked together based on an identifier tag.


Here is what I did and it seems to work, the only problem is that the mp3ID fields don't line up. That is the mp3ID for the mp3Info table doesn't match the corresponding one in the fileInfo table



'----------------------
Dim sql As String
Dim current As clsMP3Prop
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


sql = "SELECT * from mp3Info INNER JOIN fileInfo ON mp3Info.mp3ID = fileInfo.mp3ID"
rs.Open sql, DB, adOpenStatic, adLockOptimistic

For Each current In toAdd
With rs
.AddNew

!diskID = current.getDiskID
!volLabel = current.getVolLabel
!volSerial = current.getVolSerial
!diskType = current.getDiskType
!Path = current.getPath
!fileSize = current.getFileSize
!FileName = current.getFileName
!created = current.getCreated
!lastAccessed = current.getLastAccessed
!lastModified = current.getLastModified
!timesDownloaded = current.getDownLoaded

If current.getHasTag Then
'.Append "mp3ID", adInteger
!Title = current.getTitle
!Artist = current.getArtist
!Album = current.getAlbum
!Year = current.getMp3Year
!Comments = current.getComments
!Genre = current.getGenre
!FileBytes = current.getMp3Size
!Seconds = current.getLength
!MpegVersion = current.getMpegVer
!layer = current.getLayer
!bitRate = current.getBitRate
!Frames = current.getFrames
!Hz = current.getSampleRate
!Mode = current.getMode
!Private = current.getPrivate
!crc = current.getCRC
!copyright = current.getCopyRight
!Original = current.getOriginal
!Emphasis = current.getEmphasis
!Channels = current.getChannels
End If


rs.Update
End With
Next

rs.Close
Set rs = Nothing

'-----------------------------------
The code above puts everything into the right place in the right tables, except the mp3ID column in the mp3Info table doesn't match the corresponding mp3ID in the fileInfo table, it autoincrements. Other then that, it seems to work fine. Any ideas on how to overcome this problem?


Troy Williams B.Eng.
fenris@hotmail.com

 

[Terpsfan]

First check to see if both MP3 fields have the same data type. You're saying one of the first MP3 field autoincrements. I'm assuming you set that to an autonumber data type with long integer. The foreign key should be a number data type with long integer.

 

[fenris]

Thanks for the tip, but the fields are of the same type. Basically what I am trying to do is insert the data into both tables and make sure that the data in the mp3Info is related to that of the fileInfo table. So far I can get the data into both tables, but nothing is entered into the mp3ID field of the mp3Info table.

Alternatively, is there a better way to do this?


Thanks for any help....

Troy Williams B.Eng.
fenris@hotmail.com

 

[fenris]

I guess I am looking for some simple source that uses ado to put data into an access DB into two or more separte tables that are related by an ID column. The example should show proper methods of how to input data into the DB and retrieve the data. I am interested in using what the professionals use to accomplish this task.

Regards,

Troy Williams B.Eng.
fenris@hotmail.com