transfering data from SQL table into Access table

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

might not be what you wanna hear, but you can save the dts, then call it from vb. Do you want to avoid the dts all together?

Jay-235233 SSC Journeyman Points: 77 More actions · Answer 2

i want to do it progrmatically . i dont want to use DTS

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

If you use a linked table in Access to the SQL Server table it can be accesses as local Access table. Then you can transfer data from linked table.

Regards,
gova

Jay-235233 SSC Journeyman Points: 77 More actions · Answer 4

I tried this stattement but it gave following error

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

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.].

infact the mdb file is not opened or readonly or in exclusive mode and the password is also correct

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

1 of 2 options are the easiest

#1 LINK the SQL table in the Access database and then create a query there to load data

#2 Research LINKED SERVER in BOL and create one for your Access DB THEN write an INSERT INTO query/SP

Good Hunting!