Access 2003 and VB6 to add records from a file.

I am using Access 2003. I used the database splitter wizard to split the db. I have the backend on a server and the frontend on three other users' computers. I want the users to be able to click on button on a form, which will then add records from a file to one of the tables using VB6 code.
I created the code in VB6 using ADODB and tested it and it works just fine. However, when I paste the code into Access I get "User-defined type not defined" at the "Dim conn As New ADODB.Connection" statement. Here is snipet of my code:
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
conn.Open "DSN=aviation;uid=;pwd=;database=maintenance"  ' Open ODBC database

Set cmd.ActiveConnection = conn
Open "D:\Dbs\images.txt" For Input As #1  ' Open text file with index numbers

    Do While Not EOF(1)  ' Loop through all index numbers in file
        Input #1, ImgIndx   ' Get the first index number and assign to variable
        Debug.Print ImgIndx
        cmd.CommandText = "SELECT * from Maintenance"
        rs.CursorLocation = adUseClient
        rs.Open cmd, , adOpenDynamic, adLockOptimistic  ' Create and open record set
        c = rs.RecordCount
        x = ""
        If (rs.BOF = True) Or (rs.EOF = True) Then   ' check for no record found
            rs.AddNew     ' Add new record with all field data
                rs.Fields(0) = x
                rs.Fields(1) = x
                rs.Fields(2) = x
                rs.Fields(3) = x
                rs.Fields(4) = x
                rs.Fields(5) = x
                rs.Fields(6) = x
                rs.Fields(7) = x
                rs.Fields(8) = x
                rs.Fields(9) = x
                rs.Fields(10) = x
                rs.Fields(11) = x
                rs.Fields(12) = x
                rs.Fields(13) = ImgIndx
                rs.Fields(14) = 0
Who is Participating?
peter57rConnect With a Mentor Commented:
-- I get "User-defined type not defined" at the "Dim conn As New ADODB.Connection"--

That's because you need to add the ado 2.8 (or whatever version you have) reference to the database, just as you would have done in VB6.

Tools>References in any open module.

Access generally works better with DAO by the way and DAO is the only technology that is being carried forward with the newest Access versions.
wcoheeAuthor Commented:
Thanks peter57r. I appreciate it. Evidently when I clicked on a reference at begining of project it was the wrong one (it was Microsoft Add-in Designer which was right below the ADO I should have clicked on). It works fine now. I guess it was a case of cant see the forest for the trees thing. Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.