Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Data (Access 2000)

    I would like to create a button that imports/appends rows from another database. I have five fields in my database. I would like to have the same amount of rows from another database for those five fields in another database. Could someone help me get started.

    Thanks

    Deni

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

    Re: Import Data (Access 2000)

    You can use File | Get External Data | Link Tables... to create a link to the table in the other database.
    Next, create an append query based on the linked table, with the local table as target.
    If you need to do this on a regular basis, save the query, otherwise, you can discard it without saving, and delete the linked table (this will have no effect on the table in the other database, it only removes the link).

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data (Access 2000)

    Thanks Hans, can I use the following SQL string in my VBA code.

    INSERT INTO tblSamples ( SampleNumber, SampleYear, Reference, ReceivedDate, SampleType )
    SELECT Sample.[Sample Number], Sample.[Sample Year], Sample.[Sample Reference], Sample.[Date Sample Received], Sample.[Sample Type Code]
    FROM Sample;

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

    Re: Import Data (Access 2000)

    Yes, for example like this:

    Dim strSQL As String
    Dim cnn As ADODB.Connection
    strSQL = "INSERT INTO tblSamples ( SampleNumber, SampleYear, Reference, ReceivedDate, SampleType ) " & _
    "SELECT Sample.[Sample Number], Sample.[Sample Year], Sample.[Sample Reference], Sample.[Date Sample Received], Sample.[Sample Type Code] " & _
    "FROM Sample;"
    Set cnn = CurrentProject.Connection
    cnn.Execute strSQL, , adCmdText
    Set cnn = Nothing

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data (Access 2000)

    Thanks Hans,

    I get an error, the field is too small to accept the amount of data you attempted to add.

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

    Re: Import Data (Access 2000)

    Then you must open target the table in design view and adjust the field sizes.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data (Access 2000)

    Thanks again Hans,

    Hopefully last question.

    How do you change the connection string to import from another database not linked. See I need to pull data from system that has a separate database for each year.

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

    Re: Import Data (Access 2000)

    Do you mean an ADO connection? Code could look like this:

    Dim cnn As New ADODB.Connection
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CatabasesTest.mdb;"
    cnn.Open

    Or do you mean something else? If so, please explain.

Posting Permissions

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