Need Help with an insert query

  • Thread starter Thread starter DetRich
  • Start date Start date
D

DetRich

Hello,

I am writing an application which, among other things is used to store data
about our certificates. Data being inserted includes:
1. Issued To
2. Issued By
3. Expiration Date
4. Other data

What is not included above is the server that the certs are installed on.
So, I think I need to assign a value to a variable and insert that value
along with every row. Something like this:

Hostname = "server01"
INSERT INTO tblCertificates ( IssuedTo, IssuedBy, ExpirationDate,
IntendedPurposes, FriendlyName, Status, CertificateTemplate, Hostname )

I know the syntax isn't correct, but how can I accomplish this?

Thanks very much,
Rich
 
I assume that tblCertificates has the IssuedTo, IssuedBy fields

What values are you going to put in those fields and where are you going to
get them from

You should finish your sql code with

SELECT IssueTo, IssueBy........ FROM TheOtherTable (assuming the field
names are the same


INSERT INTO tblCertificates ( IssuedTo, IssuedBy, ExpirationDate,
IntendedPurposes, FriendlyName, Status, CertificateTemplate, Hostname )

SELECT IssueTo, IssueBy, ExpirationDate, IntendedPurposes, FriendlyName,
Status, CertificateTemplate, Hostname. FROM TheOtherTable
 
Thanks for the reply.

Yes, the tblCertificates table has all the required columns.
Your queries are pretty much what I expected, however, can I, or do I need
to assign the hostname to a variable so it can be included in the insert?

Backing up to the very beginning of this process.....
I receive a spreadsheet which has all the rows which will be inserted into
tblCertificates. I import this spreadsheet into a new/temporary table. This
temp table does not have a hostname. The original spreadsheet will be named
hostname.csv and hostname is the name of the server where the certs were
installed. So, each time I run the insert (select * from temp table) and
insert all rows into the tblCertificates), I need to somehow input the
hostname so that the actual hostname value will be inserted with the rest of
the row.

Hope I explained that clearly.
 
Make a variable for the hosthame
txtHost=inputbox("Hostname")

Or have a box on your controlling form for the hosthame (texthostname)
txtHost=me.txthostname


Set up your SQL string to include that value Notice the use of single and
double quotes as I assume your hostname is text type

StrSQL ="INSERT INTO tblCertificates ( IssuedTo, IssuedBy, ExpirationDate,
IntendedPurposes, FriendlyName, Status, CertificateTemplate, Hostname ) " _

& "SELECT IssueTo, IssueBy, ExpirationDate, IntendedPurposes,
FriendlyName, Status, CertificateTemplate, '" & txtHost & "' FROM
TheOtherTable"

docmd.runsql strsql, false
 
Back
Top