I have couple of problems. I am creating a MP3 player that scans the HD for MP3's and puts them into an Access database (XP, hope this doesn't make a difference). Anyway, I can use this method below to get the data into the database:
And it will actually get the data into the tables and fields that I need exactly. But my problem with this is I read the records in thru SQL:
strSQL1 = "SELECT [Album].* FROM [Album] WHERE Artist= '" & T1_A & "' ORDER BY [Album].[Album]"
Set RS = DB.OpenRecordset(strSQL1, dbOpenDynaset)
Set Data2.Recordset = RS
But when I select a record, it will give me a run-time error 3075. If I comment out the "Set" commands and put in say a MsgBox (strSQL1) instead, it will display the following:
SELECT [Album].* FROM [Album] WHERE Artist='Britney Spears
And nothing else after the name. It should have a ' ORDER BY [Album].[Album] following, but not matter what I try it won't.
If I go into the database and retype the exact same information on top of what was there, it will then work fine, example, if the recordset adds the record "Britney Spears" to the database with the code at the top, and I retype it, it will then work perfectly.
So instead of using the recordset methods, I decided to try and use the SQL Insert Into command:
But the problem I get with this is that it searches through all the records, but when I look at the actual database, nothing is in it at all, for any table or records.
First your post is unreadable, please learn to use the vbcode tags, which will keep your code formatted when you paste it in. Just do [Highlight=VB] All of your code here ['/'vbcode] (The single quotes were to keep the html parser from actually using the tags. Usually when people see code like yours they will just skip on by instead of getting a headache trying to read it.
Second you should never name a table and a field in that table the same thing. The same way you use a naming convention in code you should use one in your database. I append tbl in front of all my tables, like tblArtist. So when you say Artist people know you are talking about the field Artist as opposed to the table.
Third I would not give up on the original way you were getting your records. Figure out why you are getting the run-time error instead of immediately discarding that and trying a new way. You usually learn more that way. Also try re-posting your code using the vbcode tags...
Sorry on the first, I did notice that after it was posted. What I did was just copy the whole thing from when I posted it at visualbasicforums.com and it lost it formatting. I didn't want to retype everything and thought it would work by just doing that.
On your second comment,
It worked in the past and technically will as long as the coding is correct for accessing the database so I haven't changed it as of yet. After I get most of this working, I will look at redoing it to look more professional, but this is just a side task of mine right now.
On the third,
By posting that in there, I was hoping somebody may be able to help me one way or the other, as I do get farther with the Data1.Recordset code. But both come into problems and I figured since SQL is faster, I may be better off just trying to do it that way. I have searched the different forums and newsgroups with searching on the 3075 run-time error, but it is too general of an error, and I haven't found anything that has been posted to help me.
This is an example, it pretty much comes up with the same stuff that the other one does, anything after the actual string that is being pulled in, doesn't show up.
INSERT INTO [Artist] (Artist) VALUES ['Britney Spears
as you can see, it should pretty much do the same thing just add the ending '].
INSERT INTO [Artist] (Artist) VALUES ['Britney Spears']
with sqlSTR6 & sqlSTR7, it does the same thing, anything past the first string does not get displayed in the MsgBox. That is where I believe the hang up is.
I have attached the database. It isn't the cleanest, still trying to figure out the appropriate relations between the three tables, but I don't feel that the relations and the actual way the tables are setup is the problem.
I am trying to Trim any code the is after the actual statement. One thing I should mention is I have code in the search part of the MP3's that if it doesn't have the appropriate ID3 tags, it will put "Unknown" in it's place, and that does get entered appropriately into the database with the full SQL statement.
If Trim(Artist) = "" Then
Artist = "Unknown"
End If
If Trim(SongName) = "" Then
SongName = "Unknown"
End If
If Trim(Album) = "" Then
Album = "Unknown"
End If
Else
Artist = "Unknown"
SongName = "Unknown"
Album = "Unknown"
So it will say ['Unknown'] and get added to the database accordingly.
Just displayed T1 the Artist name. Maybe the problem is with the code to pull the ID3 tag. I will post it below.
Sub SearchFiles(ByVal objFolder As Folder, ByVal Extention As String)
On Error Resume Next
For Each objSubFolder In objFolder.SubFolders
SearchFiles objSubFolder, Extention
Next
For Each objFile In objFolder.Files
If Right(objFile.Name, 3) = Extention Then
N = N + 1
ReDim Preserve typeMP3Info(UBound(typeMP3Info) + 1)
colFoundFiles.Add objFile.Name
FileName = objFile
Open FileName For Binary As #1
Get #1, FileLen(FileName) - 127, MTag
If Not MTag = "TAG" Then
Close #1
HasTag = False
Else
HasTag = True
End If
If HasTag = True Then
Get #1, , SongName
Get #1, , Artist
Get #1, , Album
Get #1, , Year
Get #1, , Comment
Get #1, , Genre
Close #1
If Trim(Artist) = "" Then
Artist = "Unknown"
End If
If Trim(SongName) = "" Then
SongName = "Unknown"
End If
If Trim(Album) = "" Then
Album = "Unknown"
End If
Else
Artist = "Unknown"
SongName = "Unknown"
Album = "Unknown"
End If
typeMP3Info(N).Artist = RTrim(Artist)
typeMP3Info(N).Title = RTrim(SongName)
typeMP3Info(N).Path = Left(FileName, Len(objFile) - Len(objFile.Name))
typeMP3Info(N).Album = RTrim(Album)
T1 = RTrim(Artist)
T2 = RTrim(Album)
T3 = RTrim(SongName)
T4 = RTrim(FileName)
sqlSTR5 = "INSERT INTO [Artist] (Artist) VALUES ('[" & Replace(T1, "'", "''") & "]')"
sqlSTR6 = "INSERT INTO [Album] (Artist, Album) VALUES ('[" & Replace(T1, "'", "''") & "]','[" & Replace(T2, "'", "''") & "]')"
sqlSTR7 = "INSERT INTO [Main] (Artist, Album, Title, AudioLink) VALUES ('" & Replace(T1, "'", "''") & "', '" & Replace(T2, "'", "''") & "', '" & Replace(T3, "'", "''") & "', '" & Replace(T4, "'", "''") & "')"
MsgBox (T2 & "XXXXX")
Set db = DBEngine.Workspaces(0).OpenDatabase(".\db1.MDB")
db.Execute sqlSTR5
Data2.Refresh
db.Execute sqlSTR6
Data3.Refresh
db.Execute sqlSTR7
Data1.Refresh
End If
Next
End Sub
I looked at some other MP3 programs that were pulling ID3 tags, the only difference I really saw was that they had a . in front of the string names, like .Artist instead of Artist.
Works perfectly now. Thanks for all your help as this has been driving me nuts for the last couple of days and finally it came to me to test out this and worked.
Looking at your sub I see you don't dim any variables, like
VB Code:
Dim T1 As String
Dim T2 As String
Dim T3 As String
which could have been the problem. And using "On Error Resume Next" is often just an escape from errors that you should deal with in other ways.
Personally I would recomend SQL instead of "Data1.Recordset.Fields("Artist") = Text1.Text". Now having "Set db = DBEngine.Workspaces(0).OpenDatabase(".\db1.MDB")" in your sub is not a good idea. You should have it in the startup of the program and have db a public variable. Note that if you do not have
[Highlight=VB]
db.Close
Set db = Nothing
[/VBOCDE]
then the program takes up memory for each instance of db and does not let it go.
Thanks for the info. I did have the Dim's up at the top of the program which I didn't post. But I forgot completely about closing the database. I will add those to the code. I do agree that using SQL is faster and will continue looking into it. Another post on a different website said he thought it could be a NULL character after the name which is what I thought it was. But don't know how to eliminate it.
SQL Insert Into Doesn't Insert Any Data
Reply With Quote