[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

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
            rs.Update
       
            rs.Close
            cmd.Cancel
       
0
wcohee
Asked:
wcohee
1 Solution
 
peter57rCommented:
-- 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.
0
 
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.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now