Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 August 4th, 2005, 09:31 AM
Registered User
 
Join Date: Aug 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Accessing 2 Access Databases using VBA

Hi Everyone,

I am trying to add VBA code behind my form that will let me access 2 different Access databases. The first database is called Original_Test (this has a table called tblMASTER) and the second is called Copy_Test(this has a table called tblUSER).

I have the below code:

SQL1 = "INSERT INTO tblUSER select user_id,dept, group from Original_Test.tblMASTER where user_id = '" & txtUserID & "'"
db1.Execute SQL1

When I run this, I get an error saying that the tblMASTER is not found in the database. Can anyone help me resolve this?

Thanks in advance for you help:)

 
Old August 4th, 2005, 10:43 AM
Authorized User
 
Join Date: Jun 2004
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

can you post more of your code... like where you declare your DB's?

"Life is a Database"
 
Old August 4th, 2005, 10:58 AM
Registered User
 
Join Date: Aug 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sure...Below is my code:

Private Sub Command0_Click()

    Dim db As Database
    Dim db1 As Database
    Dim wrkJet As Workspace
    Dim SQL As String
    Dim SQL1 As String


    Set wrkJet = CreateWorkspace("NewJetWorkspace", _
          "admin", "", dbUseJet)
    Set db = wrkJet.OpenDatabase("C:\Documents and Settings\Original_Test.mdb")

    Set db1 = wrkJet.OpenDatabase("C:\Documents and Settings\Copy_Test.mdb")


    SQL1 = "INSERT INTO tblUser select user,dept, group from tblMaster where user = '" & txtUserID & "'"
    db1.Execute SQL1

End Sub



Thanks!!

 
Old August 4th, 2005, 12:58 PM
Authorized User
 
Join Date: Jun 2004
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

are you sure you're not mixing up which database/table has which fields? I tried your code and it works. Also, make sure you have DAO library loaded in your References.

"Life is a Database"





Similar Threads
Thread Thread Starter Forum Replies Last Post
Accessing Network Share with Access VBA JonniP Access 15 January 5th, 2007 01:57 PM
Accessing a VBA module's method from VBScript hamffjs Access VBA 3 November 28th, 2006 09:27 AM
Accessing USB SAMBA GSM Modem From VBA ysiline Access VBA 0 January 18th, 2005 07:43 AM
Accessing AS400 DB2 Library thru Excel-VBA amjad_mahmood Excel VBA 0 January 3rd, 2005 08:37 AM
accessing Macros properties with vba rle123 Access VBA 0 July 17th, 2003 04:59 AM





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