Copy ODBC table to Access existing table?

[VAer]

1st Question: For example, I have Access Table name ABC as Destination Table Name, but below code does not insert data into existing table, instead, it creates a new table ABC1 . If I run the same program second time, it creates another table ABC2 , and so on .....



How to not create new table? I would like to add data to existing table ABC .

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
& "DATABASE=pubs", acTable, "Source Table Name", "ABC", False, True

2nd question: How to change a value in a record in ODBC Database Table? Assume there is a field with unique value. UPDATE ODBC_table_name SET column2 = value2, WHERE column1=value1; How can I use the code along with DoCmd ?

3rd Question: How to add records from Access Table Name ABC to ODBC Database Table Name?

Thanks.

[Micron]

1) You can only transfer a table with no data, or the table with data when using this method. Once you have created the table, you'd either
- use the TransferSpreadsheet method or
- Automation (control one app from another by using code)
- or create an import specification (or do this manually each time; see Ribbon. My version of how to create this may be different than yours)
- or link to the spreadsheet/ODBC table.

2) and 3)
link the ODBC table(s) and use queries (preferred to working in the table itself) to perform operations (append, edit) on the linked tables

[Minty]

I'm think this is a continuation of this thread https://www.accessforums.net/showthread.php?t=73228 , I've no idea if it was ever solved.

[VAer]

I'm think this is a continuation of this thread https://www.accessforums.net/showthread.php?t=73228 , I've no idea if it was ever solved.

There are two additional questions in this thread, previous thread has only one question.

The old post is resolved now. Old post has been a few weeks, it was not resolved then, I did not use SELECT INTO, no matter how I twisted, it did not work. It is just resolved by using DoCmd.TransferDatabase

Thanks.

[VAer]

1) You can only transfer a table with no data, or the table with data when using this method. Once you have created the table, you'd either
- use the TransferSpreadsheet method or
- Automation (control one app from another by using code)
- or create an import specification (or do this manually each time; see Ribbon. My version of how to create this may be different than yours)
- or link to the spreadsheet/ODBC table.

2) and 3)
link the ODBC table(s) and use queries (preferred to working in the table itself) to perform operations (append, edit) on the linked tables

I am new to Access Developer, I am trying to make my questions as clearly as possible.

1) When you talk about lining to ODBC table, does it mean I use acLink (rather than acImport) ?

2) After linking the table, does Append or Edit modify the ODBC table data or just modify imported table in Access? After running acLink, everytime I modify something in this imported table, will the ODBC database table be updated?

3) Let us say I have linking the ODBC table at 10 am using the code acLink, in the same Access Application, I am planning to run Append or Edit code at 10:15 am. During this 15 minutes period, someone else(colleague) may modify ODBC table ABC data. Before running Append or Edit, how can I write code to refresh linked table ABC? Just making sure the data is up-to-date right before running Append or Edit. Or at least I delete linked table, and re-import the table right before running Append or Edit. That being said, I would much prefer to use SQL to interact with ODBC table directly, I mean using something like DoCmd.RunSQL But I still cannot figure out how to write SQL code which can connect to ODBC table.

4) Code in above post: If existing table name ABC exists, it always create new table ABC1, ABC2, ..... , if there is no good way to go around it, how can I check if the table ABC exists or not, if exists, then delete the table before running DoCmd.TransferDatabase everytime?

Thanks.

[Minty]

Micron is off-line so I'll try and answer some of your questions.

2) A linked table is not an imported table, it is normally(but not always) a table you have live direct access to, but not stored directly in your database. If it is editable (and this depends on the connection and table type), then any changes you make are made directly in the live linked data.

3) If it is a fully writeable ODBC linked table then this doesn't apply - you are seeing the live table data.

4) If you are dealing with a live linked table this again doesn't apply.

[VAer]

Micron is off-line so I'll try and answer some of your questions.

2) A linked table is not an imported table, it is normally(but not always) a table you have live direct access to, but not stored directly in your database. If it is editable (and this depends on the connection and table type), then any changes you make are made directly in the live linked data.

3) If it is a fully writeable ODBC linked table then this doesn't apply - you are seeing the live table data.

4) If you are dealing with a live linked table this again doesn't apply.

Thank you for your time. Regarding "live data", I will try some code and see how it works.

Since I am new, let me confirm some questions (I am trying to ask plainly):

1) Do I need to use acLink (rathen than acImport) in order to link the table?

2) Okay, you said I am seeing live data from ODBC database? So my question is not really about seeing. Let us said I click a button to link the table at 10 am, each record is unique. At 10:07 am, someone else delete that record. At 10:15 am, I click a button trying to update the record (1st Step: Checking if the record is available; 2nd step: Update the record if it is available). From 10 am to 10:15 am, I did not take a look at the linked table, so when I run the program at 10:15am, am I dealing with linked table data at time point 10:00am (the record is still available) or dealing with linked table data at time point 10:15 am (the record has been deleted at 10:07 am by someone else).

[Minty]

1) You would link once to the table only. Once it's linked it stays as a table def in the database, just like a local table. No need to use the transfer command at all. You may want to relink it on opening the database depending on your set up, but as you appear to have a DSN set up this would not be required.

2) It should behave like any other table, so it would probably appear on your form as #Deleted before you could do anything with it.

Hope that helps.

[VAer]

1) You would link once to the table only. Once it's linked it stays as a table def in the database, just like a local table. No need to use the transfer command at all. You may want to relink it on opening the database depending on your set up, but as you appear to have a DSN set up this would not be required.

2) It should behave like any other table, so it would probably appear on your form as #Deleted before you could do anything with it.

Hope that helps.

I do not want to manually link the ODBC table by myself. I am talking about using VBA code for Access Application. I have a form allowing users to enter their username/password, the users click a button to link the table by themselves (by using their own username/password). If not using transfer command, what code should I use in order to link the ODBC table?

Thanks.

[Minty]

This function will connect a table via ODBC. (Blatantly stolen from the internets somewhere, sorry I can't remember where from)
It was created for a SQL server connection but you can obviously adjust the connection string to suit your application.

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td                  As TableDef
    Dim stConnect           As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function


AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

So you would pass in the user name and password from your form, and you could hard code the server details if they aren't going to change.

[VAer]

This function will connect a table via ODBC..

Thank you.