Interaction with VB/VBA

Hi there,

I've just started learning SQL - I already know a bit of VB/VBA and my aim is to become a software developer using both SQL and VB.

One question I have is how would the average programmer go about managing the database? Are SQL commands (queries/add record/make table etc) normally programmed and controlled in VB itself via the docommand.runSQL on the event triggers or are they managed from the SQL Server application themselves? Also, if there are any other comments on how VB works with SQL I'd be very interested to hear them.

My assumption is that the database commands are managed from VB and any automated download/import or export of data into or from the SQL database is scheduled from SQL server itself.

Many thanks for your time in reading.If you use MS SQL Server 7 or 2000 you can use Enterprise Manager to manage your databases. If you use SQL Server 2005 then you can use SQL Server Management Studio to do the same.

You can connect to your SQL Server db from VB6 with ADO and with ADO.NET from VB.NET.|||Many thanks for your reply - how about if you're using MS Access for the front end?|||ADO works fine when using Access as a front end. Even better if you use an adp. You can use the majority of your VBA functions from access if you like. However, a wise developer ports the lionshare of their data-centric business logic to the database itself.|||Many thanks for your reply - how about if you're using MS Access for the front end?

If you use MS Access the only difference will be that you'll manage your data from the MS Access environment, but you will still use ADO and ADO.NET from your VB applications.|||If you are using SQL 2K and you are wise you will avoid Enterprise Manager and use Query Analyser. EM is a GUI, QA allows programmatic access to the database - much more powerful and much more flexible.

DBAs don't typically use Access or EM to manage a SQL Server db but YMMV depending on how much control you are looking for.

HTH|||If you are using SQL 2K and you are wise you will avoid Enterprise Manager and use Query Analyser. EM is a GUI, QA allows programmatic access to the database - much more powerful and much more flexible.

DBAs don't typically use Access or EM to manage a SQL Server db but YMMV depending on how much control you are looking for.

HTH

I use EM daily to run hand-built queries and I do have programmatic access to my databases. I use QA too, but for the most things EM is just fine.|||yeah but if you script everything, you are more efficient because all you have to do is it hit f5 and you have less hassles when sql goes to a new version and your ui changes.

interaction Stored Procedure - FTP-server

hi all,

this is my question:

can a stored procedure interact with a FTP-server that has nothing to do with the database the stored procedure is running from?

I want my stored procedure to delete records in my database AND my FTP-server in the same time..., so also uploaded files which have been uploaded to a FTP-server during the same session when data has been written to my database...?

this is my SP for deleting records in my database:

...

DELETE FROM dbo.tblUploadsIntern WHERE DATEDIFF(dd,GETDATE(), tblUploadsIntern.uplZipfileDateTimeOffline) > 7

END

GO

the code for deleting files on FTP-server works also...

thanks in advance...

I would not load my SQL Server with housekeeping like this.

However, you could create a SQL Agent Job that would take care of this for you.

But, really, if it has nothing to do with SQL Server, why not make a Windows Scheduler Job to do the clean up?

Interaction between tables of local and remote SQL databases

Hi all,

I would be very glad if someone can suggest me what techniques I should use in the following scenario:

I have 2 SQL Server databases : DB1 and DB2. DB 1 is on a remote server (hosting server) and DB 2 is on a local server.

Some tables of each db contain tables that are polulated and changed by the appropriate application, i.e.:

DB1.users, DB1.orders,... etc are managed by "webapplication"

DB1.products ... are not managed by "webapplication" : in fact only used to read from

DB2.products, DB2.customers, .... etc are managed by "winapplication"

DB2.orders,....are partially managed by "winapplication"

Since the amount of data can go over 100000 records i'm wondering what would by the best approach to :

- synchronize the data of DB1.products and DB2.products in DB1 on remote server (updated newly added rows and update changed rows,....)

- the products data is only (at the moment) added, edited and deleted on the local server

I think SQLBulkCopy will not do the job. Should it be possible with some query? Or...?

Any suggestions are appreciated!!

O.

Some addition:

- Does SQLBulkCopy copy-append data to the destination database.table?

- What about identity keys?

- Should it be the best solution to modify the SqlCommand so it takes only those rows I want to be inserted as (depending on bulkcopies already done before) or is this done by SQLBulkcopy itself....?

Please inform.

O.

Interaction between SQL2k, SQL2k5, and SQLExpress

I've got both SQL2000 and SQL2005 running side-by-side and now I want to
test a ClickOnce deployment of my app with SQLExpress. Anyone been down this
road and know whether I will step on the toes of the installed servers?Hi Earl
I have not tried this but I believe you should be ok, although it is worth
trying it first in as many different scenarios as you can get!
John
"Earl" wrote:
> I've got both SQL2000 and SQL2005 running side-by-side and now I want to
> test a ClickOnce deployment of my app with SQLExpress. Anyone been down this
> road and know whether I will step on the toes of the installed servers?
>
>

Interaction between SQL2k, SQL2k5, and SQLExpress

I've got both SQL2000 and SQL2005 running side-by-side and now I want to
test a ClickOnce deployment of my app with SQLExpress. Anyone been down this
road and know whether I will step on the toes of the installed servers?Hi Earl
I have not tried this but I believe you should be ok, although it is worth
trying it first in as many different scenarios as you can get!
John
"Earl" wrote:

> I've got both SQL2000 and SQL2005 running side-by-side and now I want to
> test a ClickOnce deployment of my app with SQLExpress. Anyone been down th
is
> road and know whether I will step on the toes of the installed servers?
>
>