SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Uploading MDB to SQL Server Programmatically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 08/07/2000 :  02:14:32  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Yashwant writes "Can I upload my local mdb files on the SQL server(and other way round) on the Web Programmatically without using SQL server Desktop for DTC/Import or export utility. I am using VB as my local front end and ASP as my front end for web. Please Help --ASAP"

Article Link.

Starting Member

0 Posts

Posted - 08/22/2000 :  18:44:22  Show Profile  Reply with Quote

By "DTC/Import or export utility" I take it you mean DTS. I think DTS
would be perfectly suited to the job as this can be controlled
programmatically. In fact the entire package can be built within
VB/VBScript, and of course this enables you to dynamically change any
property you desire. The Wizard is just a quick and easy interface to
building your first package, but there is a complete object model
available for you to use.

Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

PS why does this posting facility set the name to anonymous?

Go to Top of Page

Starting Member

0 Posts

Posted - 08/23/2000 :  19:37:24  Show Profile  Reply with Quote
transfer database objects without using import/export utility

I want to be able to transfer database objects without using import/export utility.
Ideally i want to have a VB app that selects the tables I want to move, and moves them using a DSN-less connection.
Any ideas?

Go to Top of Page

Starting Member

0 Posts

Posted - 09/06/2000 :  19:36:26  Show Profile  Reply with Quote
Uploading Access data to SQL

If I understand this question right you want to move data from Access to SQL progrmatically. I great feature of SQL 7 is Linked Servers. You can link any ODBC compliant datasource to SQL Server and it will act as if it is a table in SQL. So you can create a Linked Server with the code:

EXEC sp_addlinkedserver
'OLE DB Provider for Jet',
----- This allows security to be turned off for this Linked Server
EXEC sp_addlinkedsrvlogin 'TMCAccess', 'false'

Then you can get to the data like this:

, Text
, Text

Then you can delete the Linked Server like this:

IF EXISTS(SELECT * FROM master..sysservers
WHERE srvname = 'TMCAccess')
EXEC sp_dropserver 'TMCAccess', 'droplogins'

Hope this helps.

Greg McCarty

Go to Top of Page

Starting Member

1 Posts

Posted - 09/10/2003 :  04:03:23  Show Profile  Reply with Quote
I tried used
cnAExecute "INSERT INTO UserInfo SELET * FROM UserInfo IN " & """" & sLocation & """"

in visualbasic but an error occur said incorrect syntax near the keyword 'IN'.
sLocation = "c:\user.mdb"

Go to Top of Page

Starting Member

1 Posts

Posted - 07/13/2004 :  11:37:27  Show Profile  Reply with Quote

I have the same problem that you.

I using a sql database that I created the same structure of the table in my ACCESS Table.
I using linked server.
At this point, all are ok.
In my SQL databse, I create a view of my linked table of ACCESS.
At this moment , I can see all records from my ACCESS DB.
But now, I would like to use a TRIGGER in my VIEW for retrieve if a new record was inserted in my ACCESS TABLE and inserte it in my SQL table.
I create a Trigger in my View :

the code is :

---------------------------------------------------------- INSERT in Log
@Log_Date datetime,
@Log_Input smallint,
@Log_Level smallint,
@Log_Milliseconds smallint,
@Log_Output smallint,
@Log_Panel smallint,
@Log_Response smallint,
@Log_Status smallint

set @Log_Date =GETDATE()
set @Log_Input =1
set @Log_Level =1
set @Log_Milliseconds =1
set @Log_Output =1
set @Log_Panel =1
set @Log_Response =1
set @Log_Status =1

exec InsertLog @Log_Date,@Log_Input,@Log_Level,@Log_Milliseconds,@Log_Output,@Log_Panel,@Log_Response,@Log_Status

ENDBut nothing when a new record was inserted in my ACCESS TABLE.

Can you resolve my problem ?

Best regards
Go to Top of Page

Starting Member

1 Posts

Posted - 12/08/2006 :  03:34:59  Show Profile  Reply with Quote
worked nice, thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000