Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    tranferring data from one database to another (97)

    I have the following piece of code that takes info from an acces database and I'm using it as a globule module in another access database. I want to use it to add information to an existing table in the database but its not working when I call the function, can anyone see any problems I might be missing?

    Function to get information (global module):

    Public Sub Get_info(strSON As String)
    Dim dbdatabase1, dbdatabase2 As Database
    Dim recdatabase1, reecdatabase2 As Recordset
    Dim strSQL As String


    strSQL = "SELECT tblinfo.Feild1, tblinfo.field2, tblinfo.field3, tblinfo.field4, tblinfo.field5, tblinfo.field6, tblinfo.field7, tblinfo.field8 FROM tblinfo WHERE (((tblinfo.feild1)='" & strSON & "'));"

    ' This code attaches to the database1 and creates a recordset of the data specified in the SQL above
    Set dbdatabase1 = DBEngine.OpenDatabase("P:database1.mde")
    Set recdatabase1 = dbRM.OpenRecordset(strSQL)

    Set dbdatabase2 = CurrentDb
    ' Assumes the Table created to hold the Project Data is called "XtblProj1234567" where 1234567 is the Sales Order
    Set recdatabase2 = dbdatabase2.OpenRecordset("Xtblproj" & strSON)


    'This code cycles through all records in the database1 and adds them to the Project Table
    With recdatabase1
    .MoveFirst
    While Not .EOF
    recdatabase2.AddNew
    'Codes goes in here to add each record to the table
    recdatabase2.Update
    .MoveNext
    Wend
    End With

    End Sub

    Line of code to call it in class module:


    Call Get_info(" & Me.txtSalesOrder & ")




    I'm getting an error saying that it can't find hte input table in the get_info funciton, so the error is in this line of hte get_info function:

    Set recdatabase2 = dbdatabse2.OpenRecordset("Xtblproj" & strSON)


    Its kind of difficult to explain my problem, but if anyone can help me with this I'd be really grateful. thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tranferring data from one database to another (97)

    1. The following declarations

    Dim dbdatabase1, dbdatabase2 As Database
    Dim recdatabase1, reecdatabase2 As Recordset

    mean that dbdatabase1 is NOT declared as a Database, but as a Variant, and that recdatabase1 is NOT declared as a Recordset, but as a Variant. This is an annoying quirk of Visual Basic: you must declare the type of each variable separately:

    Dim dbdatabase1 As DAO.Database, dbdatabase2 As DAO.Database
    Dim recdatabase1 As DAO.Recordset, recdatabase2 As DAO.Recordset

    2. I don't know if you typed all this code into your post or copied and pasted it, but there are several potential typing errors:
    - reecdatabase2 instead of recdatabase2
    - is the first field really called feild1 instead of field1?

    3. The use of quotes in the line

    Call Get_info(" & Me.txtSalesOrder & ")

    is wrong - Get_Info will use the LITERAL text between the quotes as strSON. You don't need the quotes at all here:

    Call Get_info(Me.txtSalesOrder)

    or even

    Get_info Me.txtSalesOrder

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tranferring data from one database to another (97)

    thanks Hans, the third one was the problem, its working now without giving me any error messages, still not doing exactly what I want though so I'll have to look at it again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •