Data transfer from SQL Server to MS-Access tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Data transfer from SQL Server to MS-Access tables

Author

Topic

firozz
Starting Member

5 Posts

Posted - 2005-06-02 : 05:45:35

Hi,

I want to transfer data from SQL server table to MS-Access table programmatically.
what we do basically is loop through the recordset of teh sql table and do a field by field insert in access table.

like this:

Dim db As DAO.Database
Dim SQLTable As ADODB.Recordset
Dim Accesstable As DAO.Recordset
Dim str As String

Set db = CurrentDb()

db.Execute ("Delete from <access table>")

str = "<select data from SQL Server table>"

Set SQLTable = New ADODB.Recordset
SQLTable.Open str, gDBCon, adOpenStatic, adLockOptimistic

str = ""
If Not (SQLTable.EOF) Then
Set Accesstable = db.OpenRecordset("select * from access table with structure same as SQL Server table")
While Not RsData.EOF
Accesstable.AddNew
Accesstable!<field1>= SQLTable("<field1>")
Accesstable!<field2>= SQLTable("<field2>")
Accesstable.Update
SQLTable.MoveNext
Wend
endif

is there anyway to do the above process in a single step other than looping through and inserting one by one??
plz help.

use118
Starting Member

3 Posts

Posted - 2005-06-02 : 06:10:26

Never worked with access, but if you can see your access Db in SQL Query Analyser can you just do
insert into ACCESSTABLE select top 100 * from SQLTABLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 07:15:14

Create a table in Access with same structure of SQL Table
Then from Query Analyser of SQL Server, run this

INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0','Data Source="D:\Test.mdb";
User ID=Admin;Password='
)... AccesstableName(col1,col2,col3,...coln)
SELECT col1,col2,col3,...coln FROM SQLServerTableName

Madhivanan

Failing to plan is Planning to fail

firozz
Starting Member

5 Posts

Posted - 2005-06-02 : 07:32:50

quote:
Originally posted by madhivanan

Create a table in Access with same structure of SQL Table
Then from Query Analyser of SQL Server, run this

INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0','Data Source="D:\Test.mdb";
User ID=Admin;Password='
)... AccesstableName(col1,col2,col3,...coln)
SELECT col1,col2,col3,...coln FROM SQLServerTableName

Madhivanan

Failing to plan is Planning to fail

Hi there,
Thankx for the info.
I tried your solution but it is giving an error as follows:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

though i applied the required service pack for access, the error pertains

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 07:41:23

Are you Access2000 or 97?
If Access97, then try to use 'Microsoft.Jet.OLEDB.3.51' at the query

Look for OpenRowset in BOL for more details

Madhivanan

Failing to plan is Planning to fail

firozz
Starting Member

5 Posts

Posted - 2005-06-02 : 07:44:45

quote:
Originally posted by madhivanan

Are you Access2000 or 97?
If Access97, then try to use 'Microsoft.Jet.OLEDB.3.51' at the query

Look for OpenRowset in BOL for more details

Madhivanan

Failing to plan is Planning to fail

m using Access 2000. i checked OPENROWSET also. it gives the same error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 07:52:59

sounds that you opened the Access and run that query.
Close the Access and run the query

Madhivanan

Failing to plan is Planning to fail

firozz
Starting Member

5 Posts

Posted - 2005-06-02 : 08:25:10

quote:
Originally posted by madhivanan

sounds that you opened the Access and run that query.
Close the Access and run the query

Madhivanan

Failing to plan is Planning to fail

well, it wuz closed while executing the query??

firozz
Starting Member

5 Posts

Posted - 2005-06-02 : 10:19:47

quote:
Originally posted by firozz

quote:
Originally posted by madhivanan

sounds that you opened the Access and run that query.
Close the Access and run the query

Madhivanan

Failing to plan is Planning to fail

well, it wuz closed while executing the query??

intead of OLE can i use ODBC drivers.. if so plz temme how?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-06-02 : 10:56:31

You may also consider using DTS to export the data. If this is a repeating task, you can either schedule it in SQL Server, or code it in front end app to kick off the process.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources