Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 31st, 2004, 11:16 PM
Registered User
Join Date: Mar 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default using msde and sqlserver with access front-end

I originally posted this in ASP forum - probably better here ...

I have an Access front end to an msde db on the pc, & wanting to access a sqlserver db on the network.

If I setup the connection to the msde db (File>Connection) then that is the currentproject.connection, & I can reference tables directly.

To get the sqlserver db I create an adodb.connection:
    Set mConn = New ADODB.Connection
    strConn = "Provider=Microsoft OLE DB Provider for Sql Server;" & _
      "Data Source=Epicor;Initial Catalog=TestRig;" & _
      "Persist Security Info=False; ;Integrated Security=SSPI;"
    mConn.Open strConn
 & that is fine.
Then I can create a recordset from that db:
    rst.Open strSql, mConn, adOpenStatic, adLockReadOnly

How can I INSERT INTO the sql server db ?

I see 3 possible options (none of which I've been able to get working):

1. Is there a syntax for prefixing the table name with server & database ?

2. Can I retain connection to the msde db & link to the sqlserver tables ? If so, can that be done in vba ?

3. My requirement is either to read from the msde db & write to the sqlserver db or vice versa.
Is it possible, in vba, to change the db that is the currentproject.connection ?

Old April 1st, 2004, 04:00 AM
Authorized User
Join Date: Mar 2004
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts

I am using VB and there can be two or more connections at a time to independent databases. If you can make two connections, your problem is solved.

Lucian Preda

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple SQL Databases for Same Access Front End darrenb Access 0 May 27th, 2008 12:07 AM
Ms Access front End with Oracle 10g Back End rahul123 Oracle 1 July 9th, 2007 01:03 AM
Oracle back-end MS-Access 2003 client front-end Corey Access 2 February 16th, 2007 08:31 AM
Oracle Back End - MS Access Front End - Multi User ckaliveas Oracle 1 February 1st, 2007 06:00 AM
poor perfomance using Access front end to SQL tanjuakio Access 24 April 19th, 2005 07:21 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.