Access 2003 and VB6 to add records from a file.

Posted on 2011-04-28
Last Modified: 2012-05-11
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
Question by:wcohee
    LVL 77

    Accepted Solution

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

    Author Closing Comment

    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now