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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Uploading MDB to SQL Server Programmatically
 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

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  
 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.04 seconds. Powered By: Snitz Forums 2000