Solved

Saving data from MsAccess to Sql table

Posted on 2013-05-14
6
324 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

756 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