How to copy data from database table to Access table?

[VAer]

I mean using code, not from menu button.



For example, Access table name is: ABC_T ; Database(Sybase, it is server, not file) table name is ABC

I have code which already connects to database. Now I want to copy data from Sybase database table ABC to Access table ABC_T

Thanks.

....
....
Set Cn = CreateObject("ADODB.Connection")
Cn.Open DatabaseConnectionString  'My code works fine till here. Then how to write remaining codes?


Set ABCRs = CreateObject("ADODB.Recordset")
ABCQuery = "Select * From ABC"
ABCRs.Open ABCQuery, Cn

[Minty]

Use a SELECT INTO Query to create an copy of the table.

SELECT * INTO YourNewTable
FROM YourExistingTable

[VAer]

Thanks. But I don't think your code has the enough information, YourExistingTable is not sufficient enough. YourExistingTable is just a table name, I will need to identify it from database, otherwise, the program could try to look for Access table YourExistingTable

I think I need to use Cn somewhere to identify database.

So the whole line of code is DoCmd.RunSQL "SELECT * INTO YourNewTable FROM YourExistingTable" ?

Use a SELECT INTO Query to create an copy of the table.

SELECT * INTO YourNewTable
FROM YourExistingTable

[Minty]

Actually the most succinct way is to use the inbuilt transfer command

DoCmd.TransferDatabase acImport, "Microsoft Access", "\\YourFullDatabasepath\testdatabase1.accdb", acTable, "ABC", "ABC_T", False

This would copy table ABC from testdatbase1 into the database running the code as table ABC_T.

More documentation here http://www.blueclaw-db.com/transferdatabase-docmd.htm

[VAer]

Actually the most succinct way is to use the inbuilt transfer command

DoCmd.TransferDatabase acImport, "Microsoft Access", "\\YourFullDatabasepath\testdatabase1.accdb", acTable, "ABC", "ABC_T", False

This would copy table ABC from testdatbase1 into the database running the code as table ABC_T.

More documentation here http://www.blueclaw-db.com/transferdatabase-docmd.htm

Thanks for your reply.

First of all, I don't want the hard code on path. I am not IT, and don't know how to say the words accurately. I have database in ODBC, and I have the above code Cn.Open DatabaseConnectionString , which has already connected to database, so I am expecting to use Cn somewhere in your code. I think it is the way to identify the source table is from Sybase database.

Secondly, \\YourFullDatabasepath\testdatabase1.accdb the source database is not Access file, it is Sybase. Source data is on server, not on computer hard drive, not accurately to use file path.

[Minty]

Okay, now we are getting all the facts.

If you can open a connection to the database, then you can still use my first suggestion.
Have a read here https://msdn.microsoft.com/en-us/lib...or=-2147217396
and here http://www.vbforums.com/showthread.p...set-Into-Table

for a fuller explanation.

You don't need to hard code the table name, but you do need to provide one, same as the destination.
You could wrap the whole thing into a Public Sub in a module.

[VAer]

Okay, now we are getting all the facts.

If you can open a connection to the database, then you can still use my first suggestion.
Have a read here https://msdn.microsoft.com/en-us/lib...or=-2147217396
and here http://www.vbforums.com/showthread.p...set-Into-Table

for a fuller explanation.

You don't need to hard code the table name, but you do need to provide one, same as the destination.
You could wrap the whole thing into a Public Sub in a module.

I was not talking about hard code on table name, I was talking about no hard code on file path. But source data is not a file anyway, so no file path.

I think my first post has all the information for my question.

For the specific database, I have username/password/database name/port number/etc in DatabaseConnectionString

And I use Cn.Open DatabaseConnectionString to connect to the Sybase database

As the first post stated, ABC is a table name in Sybase database, ABC_T is the table name in current Access file, now I want to copy data from Sybase database table ABC to Access table ABC_T

The code will sit inside current access file.

The key problem of your code SELECT * INTO ABC_T FROM ABC is: There is no way to know where source table ABC is from. With your code, the program will look into current Access file for the table ABC. Actually, there is no table ABC in access file, ABC is from Sybase database.

Thanks.

[Minty]

You clearly haven't looked very well at the links provided..

They explain how to use an ODBC connection to point to the external table, and reference it in the SELECT INTO query.

You can use a syntax like this;

strSQL = "SELECT * INTO LocalTable  FROM [MS Access;DATABASE=c:\database.mdb;Jet OLEDB:database Password=mypassword;].[RemoteTableName] "

Substituting your connection string and path.

If you made a Sub or Function to do it you would need to pass the connection string , path , source table and target table as parameters to the Sub or function.

[VAer]

No, you are not understanding my question. I thought I have explained my question very clearly, multiple times. Your response is irrelevant. The source data is ODBC (Sybase), not a file in C drive, not a file on share drive, anything directing to hard drive path/share drive path is NOT what I need, in other words, there should not be PATH in the code. I have repeated that: my code Cn.Open has already connected to database, and I am expected to use Cn in the following code to identify the source data. No, you don't need to have username/password in your code, I have said username/password/database name/port number/etc in DatabaseConnectionString , user/password is not needed to appear multiple times in the program.

You clearly haven't looked very well at the links provided..

They explain how to use an ODBC connection to point to the external table, and reference it in the SELECT INTO query.

You can use a syntax like this;

strSQL = "SELECT * INTO LocalTable  FROM [MS Access;DATABASE=c:\database.mdb;Jet OLEDB:database Password=mypassword;].[RemoteTableName] "

Substituting your connection string and path.

If you made a Sub or Function to do it you would need to pass the connection string , path , source table and target table as parameters to the Sub or function.

[orange]

I am not IT, and don't know how to say the words accurately. I have database in ODBC

Don't overlook the power and usefulness of Google/Bing in researching the IT part of your project.

ABC is a table name in Sybase database, ABC_T is the table name in current Access file, now I want to copy data from Sybase database table ABC to Access table ABC_T

So you have set up an ODBC link between Access and Sybase. That should enable you to establish a link from a table definition in Access to the appropriate table in the Sybase database with all of your credentials (database, port, table, name and password...).

If your objective is to move a copy of the data in table ABC (in Sybase) to a local table ABC_T (in Access) , then it would seem a query would be appropriate.
If your objective is to learn Access and related vba and ADO, then that is a longer and more involved exercise. (ado , ADOX)

I could not find examples of ADO Connections nor ADO Recordsets using Sybase and Access, BUT here are free youtubes
showing the concepts and techniques for ADO Connections and ADO Recordsets using SQL Server and Access. Since you said you have connection strings working, you should be able to substitute SQL Server strings with your Sybase connection string(s).

Good luck with your project.

[VAer]

Don't overlook the power and usefulness of Google/Bing in researching the IT part of your project.



So you have set up an ODBC link between Access and Sybase. That should enable you to establish a link from a table definition in Access to the appropriate table in the Sybase database with all of your credentials (database, port, table, name and password...).

If your objective is to move a copy of the data in table ABC (in Sybase) to a local table ABC_T (in Access) , then it would seem a query would be appropriate.
If your objective is to learn Access and related vba and ADO, then that is a longer and more involved exercise. (ado , ADOX)

I could not find examples of ADO Connections nor ADO Recordsets using Sybase and Access, BUT here are free youtubes
showing the concepts and techniques for ADO Connections and ADO Recordsets using SQL Server and Access. Since you said you have connection strings working, you should be able to substitute SQL Server strings with your Sybase connection string(s).

Good luck with your project.

Thanks for the resource, I am going to watch WHOLE youtube playlist 41 videos. It may be good to learn something else. I will come back to this post in a few days, if I still have the issue.

[orange]

See this link if you are looking for info on Database Planning and Design.

[Minty]

No, you are not understanding my question.

I think it would be safe to say as politely as possible, that you are not understanding the answer.

The source data is ODBC (Sybase)

Yes and I showed you a method in the links to connect viaODBC to a connection of an ODBC database. You have not shown us the connection string, so I cannot create the link for you.
What I showed you was a connection to an access database as an example. Substitute your ODBC connection string in the place and I think you will find it connects.

I'm not sure using the open connection method you seem so keen on, that you can achieve what you want without looping through the entire recordset and dynamically and individually adding each field and it's contents to your new table, which would be very slow and cumbersome.

[VAer]

I think it would be safe to say as politely as possible, that you are not understanding the answer.

Yes and I showed you a method in the links to connect viaODBC to a connection of an ODBC database. You have not shown us the connection string, so I cannot create the link for you.
What I showed you was a connection to an access database as an example. Substitute your ODBC connection string in the place and I think you will find it connects.

I'm not sure using the open connection method you seem so keen on, that you can achieve what you want without looping through the entire recordset and dynamically and individually adding each field and it's contents to your new table, which would be very slow and cumbersome.

Sybase connection string is online everywhere ( https://www.connectionstrings.com/sybase-adaptive/ ), but that is irrelevant to my question, that is why I omit it, this connection string works in Access and works in Excel. I tried to avoid more confusion by not posting irrelevant information. So anything having hard drive path (C:\) is absolutely not what I need.

[Minty]

Okay last chance salon

strSQL = "SELECT * INTO LocalTable  FROM [ODBC Driver={Sybase SQL Anywhere 5.0};DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;Uid=myUsername;Pwd=myPassword;Dsn=''].[RemoteTableName] "

Do you see how that works ?

I don't know which driver you are using, as apparently that is irrelevant information.
But if you connection string looks like the one above why don't you humour me and see if the above actually functions.

Your connection string MUST by definition have a path to the database, or possibly a file DSN, so don't give me any bobbins about a file path, it's only there as an example.