SQL Insert Into Doesn't Insert Any Data

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:

Data2.Recordset.AddNew
Data2.Recordset.Fields("Artist") = Trim(T1)
Data2.Recordset.Update
Data3.Recordset.AddNew
Data3.Recordset.Fields("Artist") = Trim(T1)
Data3.Recordset.Fields("Album") = Trim(T2)
Data3.Recordset.Update
Data1.Recordset.AddNew
Data1.Recordset.Fields("Artist") = Trim(T1)
Data1.Recordset.Fields("Album") = Trim(T2)
Data1.Recordset.Fields("Title") = Trim(T3)
Data1.Recordset.Fields("Genre") = Genre
Data1.Recordset.Fields("Year") = Year
Data1.Recordset.Fields("AudioLink") = T4
Data1.Recordset.Update

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:

sqlSTR5 = "INSERT INTO [Artist] (Artist) VALUES ('[" & Replace(T1, "'", "''") & Chr(34) & "]')"
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, "'", "''") & "')"
Data2.Database.Execute sqlSTR5
Data2.Refresh
Data3.Database.Execute sqlSTR6
Data3.Refresh
Data1.Database.Execute sqlSTR7
Data1.Refresh

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.

Any help would be greatly appreciated!!!!

Thanks, Corey

Why do you use Chr(34)?

Sorry, it was just something I was trying when I was searching for possible ways to resolve my problem. The chr(24) was just a replacement for the '.

Corey

How do you connect to the database?

DAO,

Set DB = DBEngine.Workspaces(0).OpenDatabase(".\db1.MDB")

And I have also used

Data1.Databasename = ".\db1.mdb"

Try executing the "Insert Into" on the DB

VB Code:

sqlSTR5 = "INSERT INTO [Artist] (Artist) VALUES ('[" & Replace(T1, "'", "''") & ...
sqlSTR6 = "INSERT INTO [Album] (Artist, Album) VALUES ('[" & Replace(T1,  ...
sqlSTR7 = "INSERT INTO [Main] (Artist, Album, Title, AudioLink) VALUES ('" ...
DB.Execute sqlSTR5
DB.Execute sqlSTR6
DB.Execute sqlSTR7
'Data2.Database.Execute sqlSTR5 
Data2.Refresh 
'Data3.Database.Execute sqlSTR6 
Data3.Refresh 
'Data1.Database.Execute sqlSTR7 
Data1.Refresh

Also, what is Data1, Data2 and Data3?

I have several comments.

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...

Good luck

Same old same old. Does the same thing. As for the Data1, Data2, Data3 is just the Database Name variable using the Data control.

Corey

Oh, and thanks for helping.

On your first comment,

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.

Thanks for your input.

Corey

Good luck [/B][/QUOTE]

Could you post a sample of the insert strings after rendering? And attach the database file?

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

sqlSTR5 = "INSERT INTO [Artist] (Artist) VALUES ('[" & Replace(T1, "'", "''") & "]')"

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.

Thanks, Corey

Have you looked at the T1 string? Is there perhaps an extra character in the end that is causing the problem?

I doubt it, here is the code for the T1 - T4:

T1 = RTrim(Artist)
T2 = RTrim(Album)
T3 = RTrim(SongName)
T4 = RTrim(FileName)

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.

What happens if you put in the following?

VB Code:

MsgBox T1 & "xxxxx"

I tried the following:

MsgBox (sqlSTR5 & "xxxxx")

Same thing as before

MsgBox (T1 & "xxxxx")

Just displayed the Artist name

MsgBox (T2 & "xxxxx")

Just displayed the Album name

MsgBox (T1 & T2 & "xxxxx")

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.

Corey

Well I figured out a solution. I went back to the original Data1.AddNew
Data1.Recordset. blah blah
Data1.Update
code.

I did make a change though at the:

T1 = RTrim(Artist)
T2 = RTrim(Album)
T3 = RTrim(SongName)
T4 = RTrim(FileName)

I changed the T1 - T4 variables to Text box statements instead.

Text1.Text = RTrim(Artist)
Text2.Text = RTrim(Album)
Text2.Text = RTrim(SongName)
Text2.Text = RTrim(FileName)

and changed the other code to

Data1.Recordset.AddNew
Data1.Recordset.Fields("Artist") = Text1.Text
Data1.Recordset.Fields("Album") = Text2.Text
Data1.Recordset.Fields("Title") = Text3.Text
Data1.Recordset.Fields("Genre") = Genre
Data1.Recordset.Fields("Year") = Year
Data1.Recordset.Fields("AudioLink") = Text4.Text
Data1.Recordset.Update

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.

Thanks, Corey

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.

Thanks, Corey