February 13, 2004 at 10:20 pm
Hello Guys,
Is it possible to transfer data from SQL server to MS Access usign a query. For manual transfer I use dts wizard. But how to do the same task programmatically.
Will be very thankful to you.
--akki.
February 14, 2004 at 5:25 am
Yes
I have done it in otherway round
i.e SQL -> ACCESS (From VB/ADO)
'Make the conaccess,conSql Connection strings
rstaccess.Open "EventService", conaccess, adOpenForwardOnly, adLockReadOnly
rstSQL.Open "EventService", conSql, adOpenDynamic, adLockOptimistic
Do While rstaccess.EOF = False
rstSQL.AddNew
For I = 1 To 6
rstSQL.Fields(I) = rstaccess.Fields(I)
Next
rstSQL.Update
rstaccess.MoveNext
Loop
rstaccess.Close
rstSQL.Close
For each field command is not posible if there are AutoNumbers in the field.
My Blog:
February 14, 2004 at 10:28 am
Thanks. But thought that there would be a better way !!
February 14, 2004 at 11:26 am
Hi,
Yes, there is another way;
1)Create a system DSN to your SQL server in control panel ODBC
2) Add your SQL server table or view as a linked table(right click,Link tables,file type = ODBC databases and select your DSN)
3) Create a query in Access like so:
SELECT * INTO MyAccessTable
FROM MySQLView
Run the query.There is no need to create the table in Access first.
HTH
Chris
February 15, 2004 at 9:20 pm
Thanks , I will give it a try.
February 17, 2004 at 7:10 pm
You can connect your Access database to the SQL Server, create a query based on the SQL tables or views, then convert the query to a Make Table query, run it to create and populate a new Access table, then create a Delete query to empty out the new table, then create and append query to repopulate the table and then create a macro to run the delete and append queries in sequence. Make a form with button that calls the macro and you will have a quick and dirty way to get a batch of data from SQL server into an Access mdb.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy