Attach file in SQL(front-end:MS Access97 and back-end:SQL2000) (1 Viewer)

[pagaple]

plzzzzzzz hlep me

Now I'm assigned to create the function to attach file.And for my application
front-end--->MS Access97
Back-end--->MS SQL Server2000

I've known about this application about 3 months.and I'd like to ask how to create this function.I mean I'd like to save file in the temporary table in Access then transfer this data to database SQL by using the stored procedure of SQL2000.please help me on this.

Now I can create the code for select file and show path of that file in MS Access.I have 2 solutions in my mind.
1. I try to save file which is selected in the MS Access table in the OLE datatype. And then transfer that file to SQL2000 database.But it's still fail!
2.I try to create the stored procedure that input the file path and copy file to the database(SQL2000).
Thank you for all comment and helpful.

 

[pdx_man]

I'm not sure exactly what you are asking, but you have a couple of ways of getting data into SQL Server 2000. You can have a linked table to SQL by creating an ODBC DSN, then create an append query in Access to transfer data from your local table to the linked table.

You can also create a DTS package in SQL 2000 that has your Access DB as a connection, your SQL Server as a connection, then use a data transformation to import the data in. You can right-mouse click on DTS in SQL and select Import Data and a wizard will help you set this up.

You can also transfer the data using VBA, but I'm guessing you are not going to want to go that route.

 

[pagaple]

To PDX_Man
Thank you for your response.Please could you clarify more information.

My application use ODBC to connect to the database.We save the temporary data in the Access database first and then we use the stored procedure in SQL 2000 to transfer the data from temporary table to table in SQL2000.But all those data is character,number.
For this case,Please suggest me the way to transfer the data(file) to SQL database.How can I transfer data from local table to the linked table.
And for the datatype in the SQL database ,It should be the image datatype or Binary datatype.

Thank you

 

[pdx_man]

I've never tried transfering binary data from local Access to SQL. There may be some inherint issues there in the way it is stored, but I don't know.

If you have the SQL table linked in your Access database, just create an Append query in Access where the source table is the local Access table and the destination table is the linked SQL Server table.