Solved

Saving data from MsAccess to Sql table

Posted on 2013-05-14
6
317 Views
Last Modified: 2013-05-28
My client has an access app that generates and sends emails and then saves the emails to a folder on their network.  Now I need to include logic to save these locations to a SQL table.  I have done the below but nothing inserts into my SQL table and there aren't any errors:

dim db as database
dim strconnect as string
dim strsql as string

strconnect="odbc;driver={sqlserver};server=myserver;database=my database;trusted_connection=yes"
strsql="insert into [myserver].[tablename] (column1,column2) values (value1, value2);"
db.execute strsql, dbfailonerror

Why won't my record insert?
0
Comment
Question by:programmher
  • 4
  • 2
6 Comments
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 150 total points
ID: 39166116
Is this the actual VBA code you are using or just pseudo code?

Your SQL Statement doe not look correct.

What are the data types for the two columns?

If the columns are both text then you will need to use this format:

strsql="insert into [tablename] (column1,column2) values ("value1", "value2");"

Open in new window


Assuming you are substituting column1 and column2 for the actual column names, the cvaules will need to be inserted from somewhere.

Example:

dim strValue1 as string
dim strValue2 as string

strValue1 = "Data 1"
strValue2 = "Extra Data 2"

strsql="insert into [tablename] (column1,column2) values (" & Chr(34) & strValue1 & Chr(34) & ", " & Chr(34) & strValue2 & Chr(34) &  ");"

Open in new window

0
 

Author Comment

by:programmher
ID: 39175899
Hitechcoach,

The VB code is actual code.  The SQL statement is pseudo.  My SQL table has many columns.  Author_name, ISBN, publisher, published_date, etc.  
So, if I understand your comment, I need to declare all values associated with each column and use the formatting you included in your example code and that will result in my record successfully inserting into my SQL table?
0
 
LVL 21
ID: 39176251
That sounds correct. Without seeing your actual work it is hard to say if you have figured it all out.

TIP: To get the best and quickest help possible post your actual code. Replace confidential data with something that will help us understand your project.

Depending on what you are doing and how many fields you have, it may work better with a recordset.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:programmher
ID: 39189395
TheHiTechCoach,

I still can't get  my record to insert into my SQL table.  I do not get any errors.  Below is the code.    I would be happy to get the record to at least insert with null values and I can tweak the formatting.  Can you identify my error?

 Dim db As Database                    
  Dim strConnect As String
  Dim strSQL As String
  Dim strValue1 As String
  Dim strValue2 As String
  Dim strValue3 As String
  Dim strValue4 As String
  Dim strValue5 As String
  Dim strValue6 As String
  Dim strValue7 As String
  Dim strValue8 As String
  Dim strValue9 As String
  Dim strValue10 As String
  Dim strValue11 As String
  Dim strValue12 As String
 

strConnect = "ODBC;DRIVER={SQLServer};SERVER=GEMINI;DATABASE=MED_PSY1;Trusted_Connection=YES"
       strSQL = "INSERT INTO [MED_PSY1].[sysdba].[tbl_main_info] (TECHID, TECHDATE, TECH2ID, CONTACTID,SED_ID,DESCRIPTION,DescTYPE,SIZE,NAME,DATA,pharmaID,DE_pharmaID) VALUES ((" & Chr(34) & strValue1 & Chr(34) & "," & Chr(30) & StrTodayDate & Chr(30) & " ," & Chr(30) & strValue2 & Chr(30) & "," & Chr(30) & strValue3 & Chr(30) & "," & Chr(30) & strValue4 & Chr(30) & "," & Chr(30) & strValue5 & Chr(30) & "," & Chr(30) & strValue6 & Chr(30) & "," & Chr(30) & strValue7 & Chr(30) & "," & Chr(30) & strValue8 & Chr(30) & "," & Chr(30) & strValue9 & Chr(30) & "," & Chr(30) & strValue10 & Chr(30) & "," & Chr(30) & strValue11 & Chr(30) & "," & Chr(30) & strValue12 & Chr(30) & ");"
0
 

Accepted Solution

by:
programmher earned 0 total points
ID: 39192951
Rewrote module.  Closing ticket.
0
 

Author Closing Comment

by:programmher
ID: 39200680
Rewrote module.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 42
convert in derived column 7 27
Slow Connectivity over ODBC 8 32
[SQL server / powershell] bulk delete table from CSV 8 29
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question