Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-01-2016, 01:32 PM   #1
Graham63
Newly Registered User
 
Join Date: Mar 2012
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Graham63 is on a distinguished road
Question VBA Append to Another DB

Hi,

I am by no means an expert and all I know is what I have taught myself. I am creating a db to micro manage elements of projects I work on.

I have an unbound form which I found and adapted online. On the form there are 2 buttons and a text field. One button runs vba code and opens a dialog box and inserts the file path of any DB I select located on my drive in the form's text box. All good

The other button deletes table data and appends records to the file at the path in the text box. Like an export to a user DB?

My problem is the deletes work fine but I cant seem to get the variable file name/syntax right in the DoCmd.RunSQL statement, see a sample below, where am I going wrong? I am also using the variable elsewhere to OpenDatabase(strFileName) for the deletes so its right there. This is a sample of what I have. I get the error "could not find file path" With everything up to my folder and the variable string name as written not the full path string I'm looking for C:\Folder\strFileName in the error message.

Dim strFileName As String
strFileName = Me.TextBoxPath.Value

DoCmd.RunSQL ("INSERT INTO [Projects] IN 'strFileName'" & _
"SELECT [Projects].*" & _
"FROM [Projects]" & _
"WHERE (((Projects.ProjectID)=[Forms]![Projects Dashboard]![Combo519]

Thanks in advance

Graham63 is offline   Reply With Quote
Old 02-01-2016, 01:51 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,202
Thanks: 10
Thanked 3,895 Times in 3,838 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Append to Another DB

Well, not how I would do it but you have to concatenate the path:

DoCmd.RunSQL ("INSERT INTO [Projects] IN '" & strFileName & "'" & _

Also watch out for spacing between lines ("FROM [Projects]WHERE").
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 02-01-2016, 03:47 PM   #3
Graham63
Newly Registered User
 
Join Date: Mar 2012
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Graham63 is on a distinguished road
Re: VBA Append to Another DB

I Know I Know, its a hobby with me and I have started to put some of it into practice. I've read a few things on this one and I think the best route would probably have been to link the tables but I don't think the user will be able to use my network so I would be emailing the BE.

Oh, obviously this worked thanks very much. I would be interested in how you would achieve it differently if you have a moment to send me your thoughts.

Graham63 is offline   Reply With Quote
Old 02-02-2016, 09:35 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,202
Thanks: 10
Thanked 3,895 Times in 3,838 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Append to Another DB

Oh, I wasn't saying you shouldn't do it that way or anything, just that the "IN" method wasn't something I normally use. That's as much because I use SQL Server more than Access for the back end.

My first option would be linking the tables, perhaps providing the user a way to point to the db and then linking to it. My second thought was a db variable that pointed to the external database, but that wouldn't work with a mixed situation like yours (source and destination in different databases). Failing linked tables, your way is fine.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Reply

Tags
anotherdb , append , sql , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to ignore Microsoft Access can't append all the records in the append query DNASok Queries 7 03-21-2013 06:22 AM
Append Query that can append to multiple tables JuniorWoodchuck24 Queries 3 05-25-2011 12:56 AM
Append Query - Append Field keeps changing LB79 Queries 1 07-03-2009 02:09 AM
MS access can't append all the records in the append query japi_s General 3 02-19-2007 06:09 AM
Read two tables and Append to append MB Modules & VBA 4 06-03-2002 05:17 AM




All times are GMT -8. The time now is 06:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World