Unknown bug in program, please assist.

[hothead]

There's a problem that seems to lie in when my recordset puts my data inside the database, because when I type it in, it's fine, but when it gets to the database, it's like someone held the spacebar down for a couple of seconds, and data that would otherwise fit snugly inside the fields gives me an error saying that the data stream is too large (apparently because of the added spaces.)

This is the relevant code. I can add more details if needed.

Dim WkSpc As DAO.Workspace
    Dim db As Database
    Dim RecordSource As String
    Dim TableName As String
    Set rs = New Recordset
    On Error GoTo ErrorHandler
    Select Case Index
        Case 0
            If MasterControl.Text = "" Then
                MsgBox "You must specify a header in which to put the data.", vbInformation, App.Title
                Exit Sub
            End If
            RecordSource = "SELECT * FROM " & MasterControl.Text & " ORDER BY SiteName"
            ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Databases\" & MasterControl.Text & ".mdb;Persist Security Info=False"
            rs.Open RecordSource, ConnectionString, adOpenKeyset, adLockOptimistic
            If rs.BOF = False Then
                rs.MoveFirst
            End If
            For i = 1 To rs.RecordCount
                If Data(0).Text = rs!SiteName Then
                    MsgBox "Duplicate records are not allowed", vbInformation, "Record already exists"
                    Exit Sub
                Else
                    rs.MoveNext
                End If
            Next i
            rs.AddNew
            rs!SiteName = Data(0).Text
            rs!SiteURL = Data(1).Text
            rs!EMail = Data(2).Text
            rs!Category = Data(3).Text
            rs!Username = Data(4).Text
            rs!Password = Data(5).Text
            rs.Update
            rs.Close
            Set rs = Nothing
            OrganizerDatabase.ListItems.Add , , Data(0).Text

Basically, when the function starts, it checks a combobox and an array of textboxes to see if any are empty. It then checks the text in the first textbox against each record in the database for duplicate data. If duplicate data is found, it will display a messagebox and end all processing, otherwise it will continue adding the record.

[si_the_geek]

Something that worries me greatly (and could be the cause of the problem) is the first two lines of your code - which are both DAO, whereas in the rest of the code you use is ADO.

Ideally you should not have any DAO code (or reference) at all if you are using ADO; if you do need both, you should specify the library for all declarations (eg: Set rs = New ADODB.Recordset ).


There are a few other bits that are odd, such as... there is no need for rs.MoveFirst when you have just opened a recordset, as it will automatically be at the first record already.

There is also no need to loop thru the data (which is more efficient with "Do Until rs.EOF" instead), as you are looking for a particular value in the data, an appropriate query would be better, eg:

        RecordSource = "SELECT * FROM " & MasterControl.Text & " WHERE SiteName = '" & Data(0).Text & "' ORDER BY SiteName"

(note that you can still add the new record to the recordset that is returned).

[hothead]

I needed the DAO referrences in there because I seem to remember reading a website somewhere that said it was impossible to dynamically create new databases at runtime with non-Jet-native database controls (such as ADO) in VB6. Therefore, I needed the DAO referrence because DAO is Jet-native.

I don't have that website offhand though.

[GaryMazzone]

You can use ADOX to do the same thing.

[si_the_geek]

Indeed, and there are examples of that on the forums (tho I don't have a link to one handy). There may be extra features that DAO provides tho, so you may find limitations.


The point I tried to make before (which I didn't make clear!) is that ADO and DAO both contain a Recordset type.. so each time you use the Recordset type in your code, you need to specify which library to get it from (or you may get the other instead), eg:

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset

This is something that I recommend that you always do anyway for non-standard types, just to avoid potential issues (and increase the clarity when reading the code). eg:

Dim db As DAO.Database